CODEWITHSUNDEEP
sqlgroup-bycase
blue_zinc
blue_zinc

Reputation: 2500

SQL Filter based on Case

I have a table as such:

email | event

e1     a   
e1     c
e2     a
e3     b
e4     a
e4     b
e5     c
e6     d

I'm trying to create a query that gets only those who have event a exclusively

Desired output:

email  |  attended

e2       0

Here's my SQL:

SELECT
    tm.email,
    MAX(
    CASE
    WHEN tm.event = 'b' or tm.event = 'c' THEN 1
    ELSE 0
    END)
    as attended,
FROM
    table.events as tm
WHERE
    tm.event IN ('a', 'b', 'c', 'd')
    AND attended = 0
GROUP BY
    tm.email, attended

I can't seem to figure out the correct way to go about this.

Upvotes: 0

Views: 84

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

If you want emails that have only 'a', then I would use aggregation, but like this:

select email
from events
group by email
having min(event) = max(event) and min(event) = 'a';

Upvotes: 0

GMB
GMB

Reputation: 222432

You could use aggregation:

select email
from events
group by email
having max(case when event = 'a' then 0 else 1 end) = 0

Or, if you want the entire record(s), you can filter with a not exists condition:

select e.*
from events e
where 
    e.event = 'a'
    and not exists (select 1 from events e1 where e1.email = e.email and e1.event <> 'a')

Upvotes: 2

Related Questions