Oliver Whysall
Oliver Whysall

Reputation: 349

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?

Upvotes: 2

Views: 7091

Answers (3)

CyberAbhay
CyberAbhay

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

Kaii
Kaii

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

Sudhir Bastakoti
Sudhir Bastakoti

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

Related Questions