CODEWITHSUNDEEP
sqlone-to-many
Ryan
Ryan

Reputation: 1

SQL : one to many query on the many data

I'm having a really hard time figuring this out, hopefully someone can shed some light

Consider the following data

Users:

id    username    password
--------------------------
 1    Bob         123
 2    Alice       abc
 ...

Services:

id   user_id   name     status
-----------------------------
 1   1         product1   canceled   
 2   1         product2   canceled
 3   1         product3    live
 4   2         product1    canceled

I need to do a query and find all users who only have canceled services. So the response should be "alice" only as bob has at least 1 live service.

If I try filter where status!='live' then (obviously) I still get Bob & Alice, how can I do a query where all the status is canceled is this possible?

Upvotes: 0

Views: 49

Answers (3)

kites
kites

Reputation: 1405

Here is another way of approaching the problem

select distinct u.user_name from (
select user_id, sum(case when status = 'live' then 1 else 0 end) as live_cnt from services
group by user_id
having live_cnt = 0) s join users u on s.user_id =u.id
 ;

Upvotes: 0

brondy
brondy

Reputation: 35

select distinct u.username from(
select * from service where status = 'canceled') canceled
inner join users u on u.id = canceled.user_id
where canceled.user_id not in(select user_id from service where status = 'live')

It selects all users which is not in the subset with the live status

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

Using exists logic we can try:

SELECT u.username
FROM Users u
WHERE NOT EXISTS (SELECT 1 FROM Services s
                  WHERE s.user_id = u.id AND s.status <> 'canceled');

This query says, in plain English, to find any users for which we cannot find in the Services table a record for that user whose status is not cancelled.

Upvotes: 2

Related Questions