Complex SQL Help!

petemcw

Okay, I am working with a pretty intense fantasy football-based project and I am getting stumped on how to handle this the best and I could use some advice.

My main goal that I need help with is this:

I need to populate a drop-down menu with a list of all nfl players that are not currently used in any of the teams in a league.

I have a SQL statement that will return a list of all player_ids that are being used in a league, so basically I need to run another SQL statement that returns all the player_ids except the ones from the previous SQL.

Maybe an create an array with the ids used in the league, then somehow parse those out of the entire list of ids?

My brain is fried right now and any help would be awesome!!!

tsinka

Hi,

I don't know your table layout ... so just guessing:

If you have a query like

SELECT p.id,p.name FROM players p,teams t WHERE p.teamid=t.id

to get all players that are used in a league you can use this query to get all players that are not assigned to a team in a league

SELECT p.id,p.name FROM players p LEFT JOIN teams t
ON p.teamid=t.id WHERE t.id IS NULL

Thomas

mourisj

Suppose you have 3 tables:
Players:
player_id, player_name, etc.

Teams:
team_id, team_name, ...

players_in_teams:
team_id, player_id, ...

You simply query your database:

select player_id from players
minus
select player_id from players_in_teams

Of course, you may want to complexify things by adding in players_in_teams a date range from ... until (i.e. player x has been in team a from date i1 to date j1 , in team b from date i1 to date j2, etc.

Then you have to take care of some conditions:
- dont select players who do not have an until_date (as they obviously are still in a team)
- dont select players that have a until_date in the future

select player_id from players
minus
select player_id from players_in_teams
where max(until_date) < sysdate

You can also add a condition to include players who are not in a team right now, but who will be next year/month/week..

I hope I did not overlook some condition, but I think you see what I mean: I leave the details up to you.

Have a nice day,

JJ Mouris

petemcw

I think the key to my problem is the "MINUS" syntax in the sql. I have a SELECT statement that will return all the results and one that will return the results that I would like subtracted from the total.

Unfortunately, I tried it with no luck. Our server is using MySQL, and I don't believe that MySQL supports MINUS yet!!!

Looks like I am back to the drawing board.
Thanks!

tsinka

Hi,

how do the queries look like that you have so far ?

Thomas