SQL : one to many query on the many data-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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

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