CODEWITHSUNDEEP
mysql
Ryan decosta
Ryan decosta

Reputation: 455

How to select a resultset of users not belonging to a usergroup

I have two tables first is user table

id  username 

1   xyz

2   abc

and a user group table

user_id     group_id

1            2

1            3

1            5

2            2

2            3

I want to select a resultset of users which doesn't belongs to group id 5.

Upvotes: 0

Views: 24

Answers (2)

Paviel Kraskoŭski
Paviel Kraskoŭski

Reputation: 1419

SELECT DISTINCT user.username FROM user 
JOIN usergroup ON usergroup.user_id = user.id 
WHERE usergroup.group_id != 5

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

First all user ids are in group 5. Then list all users exclude these

SELECT * FROM user WHERE id NOT IN (
  SELECT user_id
  FROM group
  WHERE group_id  = 5
);

Upvotes: 2

Related Questions