SQL Joining Two or More from Table B with Common Data in Table A

Posted by Matthew Frederick on Stack Overflow See other posts from Stack Overflow or by Matthew Frederick
Published on 2010-03-31T20:47:58Z Indexed on 2010/03/31 20:53 UTC
Read the original article Hit count: 407

Filed under:
|
|

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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about mysql