I have 3 tables:
teamId | teamName
roundId | roundName
- linking table
teamId | roundId | score
I want to create a table showing rounds along the top and teams down the left with scores at the appropriate place (if they have them).
Ideally I would like 1 query to show all the data and I can then use PHP to sort through the data into the table.
How do I create a query that outputs all teams, all rounds, and scores if they have them... otherwise null
Query output example that might work:
teamId | teamName | roundId | roundName | score
1 namea 1 TV 7
2 nameb 1 TV null
3 namec 1 TV 2
4 named 1 TV 9
1 namea 2 radio 4
2 nameb 2 radio 3
3 namec 2 radio 7
4 named 2 radio 1
1 namea 3 sport null
2 nameb 3 sport null
3 namec 3 sport null
4 named 3 sport null
In the above example
- team 2 round 1 didn't play
- round 3 hasn't been played yet
This query doesnt show all the rounds:
SELECT * FROM team
LEFT JOIN score ON team.teamId = score.teamId
LEFT JOIN round ON score.roundId = round.roundId
I can then use while loops to go through the data and built the table.......
Unless anyone else has a better idea?