Hello,
I'm having quite a lot of trouble getting this select query working and what wondering if you can see what I am doing wrong.
Description:
I have two tables: hfm and hfp
hfm has a list of model numbers and part numbers relating to those model numbers. hfp lists part numbers and lists relevant information about those parts.
I am using 'part' to join the two tables. I made part on hfp as unique.
Nitty Gritty:
Say I want to list available parts, descriptions and prices for model number 110.
Here is the query I hoped would give the result I want:
mysql> SELECT hfm.model, hfm.part, hfp.description, hfp.price FROM hfm,hfp WHERE hfm.part=hfp.part AND hfm.model=110;
+-------+----------+-------------+-------+
| model | part | description | price |
+-------+----------+-------------+-------+
| 110 | 110MOTOR | MOTOR | 76.08 |
+-------+----------+-------------+-------+
This looks like it might be correct, however this gives an unexpected result.
Below is a list of parts if I search for the same model number but don't try and join the two tables:
mysql> select distinct part from hfm where model=110;
+----------+
| part |
+----------+
| 4004 |
| 4005 |
| 4016 |
| 4038 |
| 110MOTOR |
| A10-RPS |
| 760 |
+----------+
7 rows in set (0.00 sec)
Thanks in advance for any help,
Dave
I'm having quite a lot of trouble getting this select query working and what wondering if you can see what I am doing wrong.
Description:
I have two tables: hfm and hfp
hfm has a list of model numbers and part numbers relating to those model numbers. hfp lists part numbers and lists relevant information about those parts.
I am using 'part' to join the two tables. I made part on hfp as unique.
Nitty Gritty:
Say I want to list available parts, descriptions and prices for model number 110.
Here is the query I hoped would give the result I want:
mysql> SELECT hfm.model, hfm.part, hfp.description, hfp.price FROM hfm,hfp WHERE hfm.part=hfp.part AND hfm.model=110;
+-------+----------+-------------+-------+
| model | part | description | price |
+-------+----------+-------------+-------+
| 110 | 110MOTOR | MOTOR | 76.08 |
+-------+----------+-------------+-------+
This looks like it might be correct, however this gives an unexpected result.
Below is a list of parts if I search for the same model number but don't try and join the two tables:
mysql> select distinct part from hfm where model=110;
+----------+
| part |
+----------+
| 4004 |
| 4005 |
| 4016 |
| 4038 |
| 110MOTOR |
| A10-RPS |
| 760 |
+----------+
7 rows in set (0.00 sec)
Thanks in advance for any help,
Dave