Finding the id's which include multiple criteria in long format-CodePudding

Suppose I have a table like this,

id	tagId
1	1
1	2
1	5
2	1
2	5
3	2
3	4
3	5
3	8

I want to select id's where tagId includes both 2 and 5. For this fake data set, It should return 1 and 3.

I tried,

select id from [dbo].[mytable] where tagId IN(2,5)

But it takes 2 and 5 into account respectively. I also did not want to keep my table in wide format since tagId is dynamic. It can reach any number of columns. I also considered filtering with two different queries to find (somehow) the intersection. However since I may search more than two values inside the tagId in real life, it sounds inefficient to me.

I am sure that this is something faced before when tag searching. What do you suggest? Changing table format?

CodePudding user response：

One option is to count the number of distinct tagIds (from the ones you're looking for) each id has:

SELECT   id
FROM     [dbo].[mytable] 
WHERE    tagId IN (2,5)
GROUP BY id
HAVING   COUNT(DISTINCT tagId) = 2