Reputation: 349
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?
Upvotes: 2
Views: 7091
Reputation: 534
Yes this is possible to use where condition in MYSQL join queries.even you can use multiple where conditions using AND operator.
Upvotes: 0
Reputation: 20540
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
Upvotes: 9
Reputation: 100175
SELECT * FROM site_products JOIN site_trans ON (site_products.product_count = site_trans.trans_inventory AND site_products.product_id = site_trans.trans_product) WHERE 1
Upvotes: 0