JOIN Query for 2 Tables - Cannot get a valid result w/out Error

[OldWest]

I've got two tables.

 

I am trying to JOIN both tables by product_id (both tables contain this field), and WHERE status equals A in table1.

 

Here is what I have so far with absolutely no luck!

 

$query = "SELECT product_id FROM products, product_descriptions WHERE products.status='A'";

 

From what I understand, I am  SELECTing product_id from both tables (I also confirmed this much of the statement is working). FROM 2 tables as noted: products, product_descriptions. WHERE products status field equals value A...

 

Obviously my query is screwed. I've spent some hours on dev.mysql.com and have not been able to piece this together properly..

 

Thanks for any advice.

 

Does anyone know why this is not working as expected?

[DavidAM]

You have to JOIN the two tables on a common field. Otherwise, you will get a Cartesian product (every one in each table matched with every row in the other table(s))

 

$query = "SELECT product_id 
FROM products JOIN product_descriptions ON products.product_id = product_descriptions.product_id
WHERE products.status='A'";

[OldWest]

Thank you for that tip.. I was so darn close! The query you sent would not work on my version of mysql ..

 

SELECT product_id 

was required to be

SELECT product_descriptions.product_id 

 

But it appears to be working now with that small tweak. Thank you for your advice.

 

[DavidAM]

Oops. Sorry about that. Since the product_id column exists in more than on table, it has to be qualified by the table name.

[OldWest]

Mental fragmentation! I think it's part and parcel with programming.. Thanks again!