Kosmos Kosmos - 2 months ago 6
SQL Question

Is there any faster way to select users that bough thing1 and never bough thing2?

I have two tables. One has users IDs, the second user IDs and what they bough.
I trying to select users ID's (no dupes) that bough

thing1
and never bough
thing2
.

I can use this solution:

select distinct usr_id
from purchases_tbl
where productname = 'thing1'
and usr_id not in (select usr_id
from purchases_tbl
where productname = 'thing2');


But making two queries is probably not the best solution.

I tried to write this:

select acc.usr_id
from accounts acc
inner join purchases_tbl ptbl
on acc.usr_id = ptbl.usr_id
and ptbl.productname = 'thing1'
where ptbl.productname != 'thing2'


but it seems like
where
clause simply has no effect (the result still contains user IDs that bough
thing2
).

Answer

You can group by the users and then take only those having at least one time bought 'thing1' and never 'thing2'

select usr_id 
from purchases_tbl 
group by usr_id 
having sum(case when productname = 'thing1' then 1 else 0 end) > 0
   and sum(case when productname = 'thing2' then 1 else 0 end) = 0
Comments