SQL Joining Two or More from Table B with Common Data in Table A
- by Matthew Frederick
The real-world situation is a series of events that each have two or more participants (like sports teams, though there can be more than two in an event), only one of which is the host of the event. There is an Event db table for each unique event and a Participant db table with unique participants. They are joined together using a Matchup table. They look like this:
Event
EventID (PK)
(other event data like the date, etc.)
Participant
ParticipantID (PK)
Name
Matchup
EventID (FK to Event table)
ParicipantID (FK to Participant)
Host (1 or 0, only 1 host = 1 per EventID)
What I'd like to get as a result is something like this:
EventID
ParticipantID where host = 1
Participant.Name where host = 1
ParticipantID where host = 0
Participant.Name where host = 0
ParticipantID where host = 0
Participant.Name where host = 0
...
Where one event has 2 participants and another has 3 participants, for example, the third participant column data would be null or otherwise noticeable, something like (PID = ParticipantID):
EventID PID-1(host) Name-1 (host) PID-2 Name-2 PID-3 Name-3
------- ----------- ------------- ----- ------ ----- ------
1 7 Lions 8 Tigers 12 Bears
2 11 Dogs 9 Cats NULL NULL
I suspect the answer is reasonably straightforward but for some reason I'm not wrapping my head around it. Alternately it's very difficult. :)
I'm using MYSQL 5 if that affects the available SQL.
