Jump to content

Join Question


punk_runner

Recommended Posts

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";

 

 

Link to comment
https://forums.phpfreaks.com/topic/252466-join-question/
Share on other sites

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

Link to comment
https://forums.phpfreaks.com/topic/252466-join-question/#findComment-1294492
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.