Luis Cazares (8/1/2013)
You could find this article usefulhttp://www.sqlservercentral.com/articles/T-SQL/88244/
SELECT AttribIDFROM MyTable
WHERE FormatID IN (12,15)
GROUP BY AttribID
HAVING COUNT(DISTINCT FormatID ) = 2
EXCEPT
SELECT AttribID
FROM MyTable
WHERE FormatID <> 27
The article's wrong, particularly this statement:
"In SQL Server 2000 and before, you needed to use a WHERE NOT EXISTS, WHERE NOT IN, or an OUTER JOIN with a NULL filter to do the exclusion."
HAVING can handle the whole check in ANY version of SQL that supports CASE (which even SQL 6.5 supported).
SELECT AttribID
FROM dbo.MyTable
WHERE
FormatID IN (12,15,27) --list ALL values that need tested, included and excluded
GROUP BY
AttribID
HAVING
MAX(CASE WHEN FormatID = 12 THEN 1 ELSE 0 END) = 1 AND --must be found
MAX(CASE WHEN FormatID = 15 THEN 1 ELSE 0 END) = 1 AND --must be found
MAX(CASE WHEN FormatID = 27 THEN 1 ELSE 0 END) = 0 --must NOT be found
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.