CODEWITHSUNDEEP
mysqlselect
Fabiano Luiz
Fabiano Luiz

Reputation: 63

Select not related items

I have 3 tables,

 user { id, name, email }
 games { id, name, icon } 
 user_games {id_user, id_game}

I whant to select the games that the user dont have already.

This is my best try but return nothing. But at last don't give a error msg XD

SELECT a.* 
FROM games a 
LEFT JOIN user_games b ON a.id IN (b.id_game) AND '1' IN (b.id_user) 
WHERE (b.id_game IS NULL) AND b.id_user <> '1'

Please help.

Upvotes: 0

Views: 39

Answers (1)

rrehbein
rrehbein

Reputation: 4160

It looks like you mostly have it. The user_games.id_user<>1 at the end is probably not what you want, since your looking for cases where id_user is null, and null<>1 is null as well.

SELECT games.*
FROM games
   LEFT JOIN user_games on (
       games.id = user_games.id_game AND
       user_games.id_user = '1'
   )
WHERE user_games.id_user IS NULL

Upvotes: 1

Related Questions