SQL Server – Select Records with Same First Column and Different Second Column

querysql serversql-server-2016t-sql

Assume we have a SQL Server table as the following:

ContentId |  TagId
--------------------
1               2
1               3
2               1
2               2
2               3
1002            1
1002            1003
1003            4
1003            1002
1004            1
1005            2002
1006            1
1006            1003
1007            1
1007            1003
1008            2
1008            3

How can we select records with the same ContentId and TagId in 1, 1003.
In my sample result is as follows

ContentId |  TagId
----------------------
1002            1
1002            1003
1006            1
1006            1003
1007            1
1007            1003

Or

ContentId 
----------
1002                        
1006                        
1007

Best Answer

You could use intersect to get the matching records

SELECT ContentId 
FROM dbo.Content
WHERE TagId = 1003
INTERSECT
SELECT ContentId 
FROM dbo.Content
WHERE TagId = 1;

Result

ContentId
1002
1006
1007

DbFiddle

Related Question