mySQL JOIN ON WHERE?

Question

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 1

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