CODEWITHSUNDEEP
mysql
Xaver
Xaver

Reputation: 11652

Get entries which are not assigned to a given id

I have a n-n connected table and like to query the people who are not in either list 11 or 12

User

ID | user
===========
 1 | Joe
 2 | Jane
 3 | Jim
 4 | Jack

Lists

 user_id | list_id
 =================
 1       | 10
 1       | 11
 1       | 12
 2       | 10
 2       | 12
 3       | 10

The result should be

3, Jim
4, Jack

I've tried

SELECT user.ID, user.name FROM user 
   LEFT JOIN user_lists ON user_id = user.ID AND user_id NOT IN (11, 12)

which obviously gives me Jane as well.

Upvotes: 0

Views: 26

Answers (1)

Taras Velykyy
Taras Velykyy

Reputation: 1801

The inner query should do the trick:

SELECT ID, user FROM `User`
WHERE ID NOT IN 
   (SELECT user_id FROM `Lists` WHERE list_id IN (11, 12))

Fiddle as an example: http://sqlfiddle.com/#!9/67933d/2

Upvotes: 1

Related Questions