Most efficient way to write SQL-CodePudding

I am having following table of user_events:

User_ID   Event_Type
user1       A
user1       B
user2       C
user3       A
user3       B
user3       D
user3       E
..................

Output for above should be : user1,user3

I want to write SQL to fetch all user id which has Event_Type A and Event_Type B but not Event_Type C.

One way is to write 3 queries and do their intersection.

Is there a more efficient way given that user_event can have ~100k records?

CodePudding user response：

Try this:

SELECT UserID
FROM Events
WHERE Event_Type IN ('A', 'B', 'C')
GROUP BY UserID
HAVING COUNT(*) = 2 AND MAX(Event_Type) <> 'C'

CodePudding user response：

Here's an alternative query:

SELECT UserID
FROM Events
WHERE Event_Type IN ('A', 'B', 'C')
GROUP BY UserID
HAVING GROUP_CONCAT(DISTINCT Event_Type ORDER BY Event_Type) = 'A,B';

The query given in Joel Coehoorn's answer would also work for your question as described, but mine does not rely on the one you want matching MAX(Event_Type).