CODEWITHSUNDEEP
sql
timmaktu
timmaktu

Reputation: 163

Select with combinations of values

I have a table with 3 columns:

fileTagId: autoincrement, primary
fileId
tagId

multiple tags (tagId) can be associated with the same file (fileId), like so:

fileTagId | fileId | tagId
1         |1       | 4
2         |1       | 9
3         |1       | 12
4         |1       | 17
5         |2       | 4
6         |2       | 9
7         |2       | 19
8         |3       | 4
9         |3       | 7
10        |4       | 4
11        |5       | 32
12        |5       | 47
13        |5       | 49
14        |5       | 63

I wish to select all files (fileId) with a particular combination of tags (tagId) eg all files with tags 4 and 9 and it should return the following on the above table:

1
2

as only file number 1 and 2 have BOTH 4 AND 9 as tags

Upvotes: 0

Views: 36

Answers (2)

mrtig
mrtig

Reputation: 2267

You can accomplish this by using STUFF or PIVOT like so : 

SELECT DISTINCT FileId
FROM 
(
    SELECT FileId,
            STUFF((SELECT ',' + CAST(TagId as varchar)
                    FROM #FileTag b
                    WHERE b.FileId =  a.fileId
                    ORDER BY TagId
                    FOR XML PATH(''))
                , 1, 1, '') AS NameList
    FROM #fileTag a
) t 
WHERE t.NameList like '%4,%9,%'

Or PIVOT 

SELECT DISTINCT FileId 
FROM 
(
    SELECT * 
    FROM 
        (
            SELECT DISTINCT FileId, TagId 
            FROM #FileTag 
        ) a 
        PIVOT (MAX(TagId) FOR TagId IN ([4], [9])) p
) t
where [4]+[9] IS NOT NULL

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

I find that the most general way to approach these queries is using aggregating with a having clause:

select fileid
from t
group by fileid
having sum(case when tagid = 4 then 1 else 0 end) > 0 and
       sum(case when tagid = 9 then 1 else 0 end) > 0;

Each condition in the having clause counts the number of rows that match one of the tags. You want at least one of each, hence the > 0.

The reason I like this approach is because it is easy to add new conditions. Say you want 4 and 9 but not 6

having sum(case when tagid = 4 then 1 else 0 end) > 0 and
       sum(case when tagid = 9 then 1 else 0 end) > 0 and
       sum(case when tagid = 6 then 1 else 0 end) = 0;

Upvotes: 2

Related Questions