CODEWITHSUNDEEP
sql
Andy
Andy

Reputation: 373

SQL, select field if field doesn't exist in another table

I have two tables, and want to get all the Products.ProductID if it doesn't exist in Images.ProductID.

I'm not too sure how I would write this..

Any help would be great.

Upvotes: 1

Views: 1428

Answers (4)

Vivek S.
Vivek S.

Reputation: 21945

select 
     productid 
from Products 
where productid not in (select productid from Images)

Upvotes: 0

Andrea Colleoni
Andrea Colleoni

Reputation: 6021

SELECT Products.ProductID
FROM Products
WHERE Productd.ProductID NOT IN (
    SELECT Images.ProductID
    FROM Images
)

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726987

You can translate your English sentence into SQL almost directly:

SELECT * FROM Products p
WHERE NOT EXISTS (SELECT * FROM Images i WHERE i.ProductId=p.ProductId)

Upvotes: 4

juergen d
juergen d

Reputation: 204894

select ProductID
from Products
where ProductID not in
(
  select distinct ProductID
  from images
  where ProductID is not null
)

or

select p.ProductID
from Products p
left join images i on i.ProductID = p.ProductID
where i.ProductID is null

Upvotes: 0

Related Questions