show all players minus the ones in another table

subquery returns more than one row :-s

[delboy1978uk]

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]

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]

SOLVED! The word I was looking for was ALL lol!

[whoisgregg]

Glad you got it sorted. :)