CODEWITHSUNDEEP
sqljoin
jemminger
jemminger

Reputation: 5173

Find users not in an organization

Given this data:

users
  id    name
  ==    ====
  1     Alice
  2     Bob
  3     Carl

organizations
  id    name
  ==    ====
  1     Aaa
  2     Bbb

memberships
  id    organization_id   user_id
  ==    ===============   =======
  1     1                 1
  2     2                 1
  3     1                 2

I want to find users that do not belong to a particular organization X:

For example, I want users that are not in organization 2. I am expecting:

users.id
========
2
3

Attempting with this join isn't working:

SELECT *
FROM users left join memberships on memberships.user_id = users.id
where memberships.id is null or memberships.organization_id <> 1

It's returning users 1,2,3 since 1 matches on the second where condition.

Is there an efficient way to do this?

Upvotes: 1

Views: 151

Answers (5)

user596075
user596075

Reputation: 

select u.*
from users u
left join memberships m
on u.id = m.user_id
left join organizations o
on m.organization_id = o.id
where m.organization_id not in (2)

EDIT: modified to include all users

Upvotes: 0

BenR
BenR

Reputation: 12276

Here's another option using the exists clause:

 select * from users
 where not exists 
    (select memberships.id from memberships 
      inner join organizations on memberships.organization_id = organizations.id 
      and memberships.user_id = users.id
      where organizations.id = 2)

Upvotes: 0

dansasu11
dansasu11

Reputation: 913

select Users.*
from users 
inner join memberships 
on Users.id = memberships.id
left join organizations 
on memberships.organization_id = organizations.id 
where memberships.id is null  AND memberships.organization_id = 2

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52645

Restricting your JOIN to organization of 2 and then testing for null is the one way to do what you're looking for e.g.

SELECT * 
FROM   users 
       LEFT JOIN memberships 
         ON memberships.user_id = users.id 
            AND memberships.organization_id = 2 
WHERE  memberships.id IS NULL

You can also use NOT IN 

SELECT * 
FROM   users 
WHERE id NOT IN (SELECT user_id from memberships where organization_id = 2 )

Upvotes: 2

Lord Peter
Lord Peter

Reputation: 3501

You can also use the minus set operator:

select "name" from users minus select "name" from users u inner join memberships m on u.id = m.user_id inner join organizations o on m.organization_id = o.id and o."name" = 'X'

Upvotes: 0

Related Questions