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?
>Solution :
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