CODEWITHSUNDEEP
mysqlsql
Ricce
Ricce

Reputation: 69

SQL: exclude users where at least one row is matching

I have a database of users (id, name) which is connected to database of their purchases (id, userId, price). What I want to do is to find all users who didn't make a purchase with price 500. At first I though about such query, but it would return all rows where price is not 500, not the users themselves. 

select * from purchase p
join user u on u.id = p.userId
and price != 500

Does somebody have an idea how to group it so only the users who NEVER did 500 purchase would show up?

Upvotes: 0

Views: 62

Answers (1)

juergen d
juergen d

Reputation: 204766

One way is to group by the user and take only those groups having no purchase with a price of 500

select u.id, u.name
from user u
left join purchase p on u.id = p.userId
group by u.id, u.name
having sum(p.price = 500) = 0

Upvotes: 2

Related Questions