Oliver Whysall Oliver Whysall - 7 months ago 11
SQL Question

mySQL JOIN ON WHERE?

is it possible to add a WHERE into a mysql JOIN statement, for example:

$result = mysql_query("SELECT * FROM site_products JOIN site_trans ON site_products.product_count = site_trans.trans_inventory WHERE site_products.product_id = site_trans.trans_product");


is this possible?

Answer

yes this is possible, but your query may not give the desired result:

SELECT * FROM site_products 
JOIN site_trans ON site_products.product_count = site_trans.trans_inventory 
WHERE site_products.product_id = site_trans.trans_product

instead, you should write that as an additional JOIN condition like so

SELECT * FROM site_products 
JOIN site_trans ON site_trans.trans_inventory = site_products.product_count
     AND site_trans.trans_product = site_products.product_id

you may additionally also add a WHERE clause

SELECT * FROM site_products 
JOIN site_trans ON site_trans.trans_inventory = site_products.product_count
     AND site_trans.trans_product = site_products.product_id
WHERE site_products.product_id = 2