SQL SELECT non-distinct values where multiple rows match

Question

I have a junction table for many-many relationship that looks something like this:

Image_Tags:

Image_ID    |.    Tag_ID
4           |     5
4.          |     6
4           |     7
5           |     6
5           |     7

I want create a SQL query to select an Image_ID that satisfies multiple Tag_ID requirements. For example, I want to find all Image_IDs that have both Tag_ID 5 AND 6, returning image 4 but not image 5. How would I go about this?

Hi. This is (obviously) a duplicate. (And shows no research effort & as an obvious faq it is not useful.) Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. — philipxy, Nov 26 '18 at 06:00

score 2 · Accepted Answer · answered Nov 26 '18 at 03:19

2

You can use group by and having:

select image_id
from image_tags
where tag_id in (5, 6)
group by image_id
having count(*) = 2;  -- "2" is the number of tags in the IN list

answered Nov 26 '18 at 03:19

Gordon Linoff

1,242,037
58
646
786

SQL SELECT non-distinct values where multiple rows match

1 Answers1