homepage Welcome to WebmasterWorld Guest from 54.211.97.242
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
show all players minus the ones in another table
subquery returns more than one row :-s
delboy1978uk

10+ Year Member



 
Msg#: 4141656 posted 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

10+ Year Member



 
Msg#: 4141656 posted 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

10+ Year Member



 
Msg#: 4141656 posted 11:32 am on May 27, 2010 (gmt 0)

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

whoisgregg

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



 
Msg#: 4141656 posted 9:48 pm on Jun 1, 2010 (gmt 0)

Glad you got it sorted. :)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved