Welcome to WebmasterWorld Guest from 54.161.25.142

Forum Moderators: open

Message Too Old, No Replies

show all players minus the ones in another table

subquery returns more than one row :-s

     
11:23 am on May 27, 2010 (gmt 0)

10+ Year Member



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?
11:30 am on May 27, 2010 (gmt 0)

10+ Year Member



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?
11:32 am on May 27, 2010 (gmt 0)

10+ Year Member



SOLVED! The word I was looking for was ALL lol!
9:48 pm on Jun 1, 2010 (gmt 0)

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Glad you got it sorted. :)
 

Featured Threads

Hot Threads This Week

Hot Threads This Month