CODEWITHSUNDEEP
sql
larryq
larryq

Reputation: 16299

Finding rows that do not have particular child rows

I have two tables that pertain to images:

ImageInfo
---------
Id
Name
Photographer

Images
------
Id
ImageInfo_Id
Quality
URL

Images has a many-to-one foreign key relationship with ImageInfo, and can hold various qualities-- 600, 800 and other pixel resolutions, for a given ImageInfo entry.

What I'm after are all ImageInfo IDs that do not have a 600 pixel quality entry in the Images table. How can I best do this? I'm thinking of a left outer join or maybe where not exists statement but wanted to solicit opinions before going that route.

Upvotes: 1

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

It sounds like not exists

select ii.*
from imageinfo ii
where not exists (select 1
                  from images i
                  where i.ImageInfo_Id = ii.id and
                        i.quality = '600 pixel' -- or whatever
                 );

You can also phrase this as a left join, but not exists is almost a direct translation of your question.

Upvotes: 1

Related Questions