CODEWITHSUNDEEP
mysql
codeguy
codeguy

Reputation: 700

MySQL Queries - Selecting from 2 tables and if exists in the other check field

I am trying to pull products from a table called products, I also have a table called product_ranges. 

products
--------
id
name
model

product_ranges
--------------
id
product_id
other_id

SELECT p.id 
FROM products As p 
LEFT JOIN product_ranges As pr ON (pr.product_id = p.id AND pr.other_id = 16)

This will select all products and include the product_ranges table columns too if the product exists in it but if it does exist in this table and the other_id does not equal 16 I don't want the product to be in the returned results but if the product doesn't exist at all in the other table I want it in the results still.

I am sure I have done this years ago but can't think of the SQL for it - if anyone knows the right query I would be grateful, thanks.

Upvotes: 0

Views: 608

Answers (1)

Michael J. Anderson
Michael J. Anderson

Reputation: 479

Updated:

SELECT p.id 
FROM products 
LEFT JOIN product_ranges pr ON pr.product_id = p.id
WHERE (pr.product_id IS NULL OR pr.other_id = 16)

Upvotes: 1

Related Questions