Join Question

[punk_runner]

Ver 14.12 Distrib 5.0.45

 

I have a table with stores and a table with products, with a foreign key on store_id. Products belong to a store.

 

I need to pull 8 random products but only if the store they belong to has a status of 1 and visibility of 1

(some stores are hidden/private, we don't want to display those products)...

 

I don't want to add status and visibility columns to the products table, that's not normalized.

 

Here's my query, it is pulling from all stores, even if status or visibility is 0, why?

 

$_query = "SELECT products.sku, products.photo, stores.store_id FROM products, stores 
WHERE stores.status = 1 AND stores.visibility = 1 ORDER BY RAND() LIMIT 8";

 

 

[fenway]

You're missing a JOIN condition -- need to see the tables.

[punk_runner]

Yeah sorry...

 

This works I believe:

 

$_query = "SELECT products.sku, products.photo FROM products LEFT JOIN stores ON products.store_id = stores.id

WHERE stores.status = 1 AND stores.visibility = 1 ORDER BY RAND() LIMIT 8";

[fenway]

So, solved?

[awjudd]

Why are you using a LEFT JOIN?  You are eliminating the need for it by having the conditions in your WHERE clause, so really you only need it to be an INNER JOIN.

 

$_query = "SELECT products.sku, products.photo FROM products 
JOIN stores ON products.store_id = stores.id 
WHERE stores.status = 1 AND stores.visibility = 1 ORDER BY RAND() LIMIT 8";

 

~awjudd