| show all players minus the ones in another table
subquery returns more than one row :-s |
delboy1978uk
msg:4141658 | 11:23 am on May 27, 2010 (gmt 0) |
hi guys, this should be easy, but its been a while for me lol:
SELECT id, forename, surname FROM players WHERE clubid = 'ARD' AND id !=
(SELECT playerid FROM teamsheets WHERE lineupid = 12)
the teamsheets table has a list of players in a lineup
the players table has all the player details
the clubid and lineupid are irrelevant for this question
im trying to create a dropdown menu that contains all the players who arent on the teamsheet, so i can add them. obviously if someone is already on the teamsheet, their name shouldnt appear.
I tried using ANY, like this:
SELECT id, forename, surname FROM players WHERE clubid = 'ARD' AND id != ANY (SELECT playerid FROM teamsheets WHERE lineupid = 12)
but for some reason the 2 players on the teamsheet still appeared in the results :-s
can anyone help?
|
delboy1978uk
msg:4141664 | 11:30 am on May 27, 2010 (gmt 0) |
incidentally if i restrict the query to one result it works fine, eg.
SELECT id, forename, surname FROM players WHERE clubid = 'ARD' AND id !=
(SELECT playerid FROM teamsheets WHERE lineupid = 12 AND playerid = 1306)
this hides player 1306 from the results, so why wont ANY work?
|
delboy1978uk
msg:4141668 | 11:32 am on May 27, 2010 (gmt 0) |
SOLVED! The word I was looking for was ALL lol!
|
whoisgregg
msg:4145228 | 9:48 pm on Jun 1, 2010 (gmt 0) |
Glad you got it sorted. :)
|
|
|
