On 24 Mar 2013, at 7:23am, Igor Korot <ikoro...@gmail.com> wrote: > Basically what I wanted to ask was: how do I write a query to retrieve > non-drafted players in the league.
Well, you have a table of players, and a table of drafted players. > Then I can get the drafted players and it will eliminate a search > algorithm to draft players at start. > I think this solution will work even better, don't you? > > Will this query work: > > SELECT playerid, name FROM players, league, playersinleague, > drafrtedplayers WHERE players.playerid = playersinleague,playersid AND > league.leagueid = playersinleague.leagueid AND league.leagueid = > draftedplayers.leagueid AND draftedplayers.playerid is NULL AND > league.leagueid = 1; You seem to have implied some sort of JOIN ON clause in the above without actually stating it. I don't know whether that's legal in SQLite. There are many ways to do it. Some depend on how you are using your draftedplayers table. If each player only ever has 0 or 1 entry in it, in other words if it reflects your /current/ drafts only, then you could just merge it into your playerid table, and have leagueid and draftprice be NULL if a player isn't drafted yet. Another way would be to use a JOIN to find the undrafted players ... SELECT playerid FROM players JOIN draftedplayers ON draftedplayers.playerid = players.playerid WHERE draftedplayers.playerid IS NULL (or use IS NOT NULL at the end). I think that'll work. Another way is to collect a list of drafted players and see which ones are in it: SELECT playerid FROM players WHERE playerid IS NOT IN (SELECT playerid FROM draftedplayers) I doubt any of these will work exactly as I wrote them but they should give you ideas for syntax. Also I don't know how fantasy football works so I probably misunderstand your league system. But you have plenty of experimentation from the above. Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
