SQL subquery to get all rows of columns where other column value does not exist-CodePudding

I have the following Accounts_Users table:

account_id | user_id
--------------------
1          | 60
2          | 60
3          | 60
1          | 50
3          | 50 
3          | 40

And I want to retrieve all the user_id's which do not have rows with certain account_id For example if the account_id = 2 I want the result should be:

user_id
-------
50 
40 


Since user_id = 60 have record with account = 2.

How can I do it using subqueries or in any other way?

CodePudding user response：

You use a subquery:

select distinct user_id
from Accounts_Users
where user_id not in (
  select user_id from Accounts_Users where account_id = 2
)

CodePudding user response：

Subquery way:

select distinct user_id
from accounts_users
where user_id not in (
    select user_id
    from accounts_users
    where account_id = 2)

Join way:

select distinct a.user_id
from accounts_users a
left join accounts_users b on b.user_id = a.user_id
    and b.account_id = 2
where b.user_id is null