sql join 3 tables -

its a left and an inner but which way

[shumboom]

Hi

I have 3 tables to run my school football clubs they are -

Clubs: clubID,PlayerID - all players in a club

Matches: matchID,Date,clubID - all matches for a club

Available:matchID,PlayerID,Status - availability of players who have registered that they can or cannot play.Not all players will have registered.

I want to select all players and their availability Status
for a given match so the result will always be the full list of players in a club.

As I said in the title it seems to be a left join and an inner but I have tried every combination so far except the right one. Thanks for your help,Shumit

[coopster]

$matchID = 9; // Set the match ID 
$sql =  
"SELECT 
  Clubs.PlayerID, 
  Available.Status 
FROM Matches 
INNER JOIN Clubs ON (Matches.clubID = Clubs.clubID) 
LEFT JOIN Available ON (Clubs.PlayerID = Available.PlayerID) 
WHERE 
  Matches.matchID = $matchID" 
;

The "Status" for any given row in your result set will contain the status value for any players found. For any players on the team that have not registered, the LEFT JOIN is going to return a NULL value for the "Status"

You will have to test that query, I just threw it down off the top of my head.

[shumboom]

Thanks , thats something like I had but the problem with it is that the results are larger than the normal set of players, ie it gives a record for players from previous matches. When I go to select on matches however I lose the nulls.

I have now solved this with a sub-query but its not pretty:

The club is $c, match=$m

SELECT
q1.player_id,
q1.status,
FROM
(SELECT
t1.player_id,
t2.match_Id,
COALESCE( t2.status, 0 ) as status,
FROM clubs t1
LEFT JOIN available t2 ON t2.player_Id = t1.player_id WHERE t1.club_Id = $c ) q1

WHERE q1.match_Id IS NULL OR q1.match_Id=$m

It works but Im sure it can be improved. Thanks,Shum

[coopster]

Like I said, I just threw it down off the top of my head, you'll have to tweak it.

so the result will always be the full list of players in a club

the problem with it is that the results are larger than the normal set of players, ie it gives a record for players from previous matches

The statements seem to contradict each other. Perhaps you need to show us some example data from the files and a result set that you expect after a successful query has been executed.