Re: problem selecting from one table where not in another

[Israel Iglesias]

Good morning, the transfer list table should be a bit different as it needs to store the real player id as well as the user player id, but I think for this case is good as it is. Also the user_player and transfer_list tables need to store the league id.

Now I would need to select a new player from the player table that is not on user_player nor transfer_list for a given league. This I have tried with left join and also with NOT IN ( [list of ids] ), but in both cases it did not work as it would sometimes select a player that was in the NOT IN list.

Let me rewrite my query using the english names so perhaps you can see more clear what I'm doing wrong:

[code]
SELECT player.*
FROM player
LEFT JOIN
user_player AS up
ON
player.player_id=up.player_id
AND
up.league_id='30166e'
LEFT JOIN
transfer_list AS tl
ON
player.player_id=tl.player_id
AND
tl.league_id='30166e'
WHERE
player.level='1'
AND
up.player_id IS NULL
AND
tl.player_id IS NULL
ORDER BY RAND()
LIMIT 1
[/code]

As you can see the left joins match players for the given player_id AND league_id and should only return 1 player that is not in either one of the tables. Like I said, I also tried the following query:

[code]
SELECT * FROM player p WHERE p.level='1' AND p.player_id NOT IN (1, 2, 3,...) ORDER BY RAND() LIMIT 0,1
[/code]

only thing is the NOT IN sometimes would hold about 200 ids.

Thank you