Selecting a column with left join

[Shadowing]

Hey guys I need help with a select statement I can't figure out

 

I'm wanting to select users.id that does not match game.id where users.computer_player = 1

 

I tried this below which gives me 9 duplicate id's for every id selected. Can't figure out why. hoping someone can help me out. users.id and game.id are both unique index's

 

SELECT users.id FROM users LEFT JOIN game ON users.id != game.id WHERE users.computer_player = 1

 

results from this query

id 6876 6876 6876 6876 6876 6876 6876 6876 6876 6877 6877 6877 6877 6877 6877 6877 6877 6877 6878 6878 6878 6878 6878 6878 6878 6878 6878 6879 6879 6879

[jazzman1]

Did you try GROUP BY users.id ?

[Psycho]

A little explanation of what the relationship between tables is would be helpful. I don't know if there can be multiple associated records in the game table for each user. But, it seems you are wanting to find the users that have NO associated records in the game table. If so, then use this:

 

SELECT users.id
FROM users LEFT JOIN game
 ON users.id == game.id
WHERE users.computer_player IS NULL

[Shadowing]

Thanks for the replies guys

 

I got this to work using jazzman's route

 

SELECT users.id FROM users LEFT JOIN game ON users.id != game.id WHERE users.computer_player = 1 GROUP By users.id

 

pyscho return empty query.

 

columns for game.id is unique and users.id is unique. So no multiple associations.

The game table either has that id in it or it doesnt.

 

Even though group by works

 

 

so anyways my test didnt test to make sure this part works

 users.id != game.id 

but left joining it like that should only return results if there are no matches right? I'll double check here in a bit.

[Barand]

SELECT users.id
FROM users LEFT JOIN game
ON users.id = game.id
WHERE game.id IS NULL

 

If there is no match it is the fields from game that will be null

Edited February 20, 2013 by Barand