Select records with the same first column value and different second column value? SQL Server
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