ScottPletcher (8/1/2013)

---

Luis Cazares (8/1/2013)

---

You could find this article useful

http://www.sqlservercentral.com/articles/T-SQL/88244/

SELECT AttribID

FROM 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