Get this group but not that group

Fri May 29, 2009 by Peter Larsson in optimization, sql-server-2008, algorithms, sql-server-2005, sql-server-2000

Wow! This seems to be blog post number 100.

Today I stumbled across an interesting problem where OP had a set of records. Every record were "typed" and "grouped".
Now OP wanted to get distinct group id's for those groups having at least one record typed 33, but not having a record typed 11.

This can be done in a number of ways, but I wanted to write an efficiently running query.

DECLARE @Sample TABLE

(

MeetingID INT,

TicketID INT

)

INSERT @Sample

SELECT 1, 11 UNION ALL

SELECT 1, 22 UNION ALL

SELECT 1, 33 UNION ALL

SELECT 2, 22 UNION ALL

SELECT 2, 33 UNION ALL

SELECT 3, 11 UNION ALL

SELECT 3, 33 UNION ALL

SELECT 5, 22 UNION ALL

SELECT 4, 33

SELECT MeetingID

FROM @Sample

GROUP BY MeetingID

HAVING MAX(CASE TicketID WHEN 11 THEN 2 WHEN 33 THEN 1 ELSE 0 END) = 1