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

Question

NOTE: EDITED

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)

If I know there are two participants, what I'd like to get as a result is something like this:

EventID  PID-1(host)  Name-1 (host)  PID-2  Name-2
-------  -----------  -------------  -----  ------
    1         7       Lions            8    Tigers
    2        11       Dogs             9    Cats

Similarly, how would I do the same if I know there are three participants?

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.

Answer 1

CREATE TABLE event (
  pk int PRIMARY KEY
);

CREATE TABLE participant (
  pk int PRIMARY KEY,
  name varchar(255)
);

CREATE TABLE matchup (
  e int,
  p int,
  isHost bit,
  CONSTRAINT PK_matchup PRIMARY KEY (e,p)
);

-- Events with 2 particpants
INSERT INTO event (pk) VALUES (1);
INSERT INTO event (pk) VALUES (2);
INSERT INTO participant (pk, name) VALUES (7, 'Lions');
INSERT INTO participant (pk, name) VALUES (8, 'Tigers');
INSERT INTO participant (pk, name) VALUES (11, 'Dogs');
INSERT INTO participant (pk, name) VALUES (9, 'Cats');
INSERT INTO matchup (e, p, isHost) VALUES (1, 7, 1);
INSERT INTO matchup (e, p, isHost) VALUES (1, 8, 0);
INSERT INTO matchup (e, p, isHost) VALUES (2, 11, 1);
INSERT INTO matchup (e, p, isHost) VALUES (2, 9, 0);

-- Events with 3 particpants
INSERT INTO event (pk) VALUES (3);
INSERT INTO participant (pk, name) VALUES (1, 'One');
INSERT INTO participant (pk, name) VALUES (2, 'Two');
INSERT INTO participant (pk, name) VALUES (3, 'Three');
INSERT INTO matchup (e, p, isHost) VALUES (3, 1, 0);
INSERT INTO matchup (e, p, isHost) VALUES (3, 2, 1);
INSERT INTO matchup (e, p, isHost) VALUES (3, 3, 0);

-- SELECT the events with 2 participants
SELECT
  event.pk AS EventID,
  p1.pk    AS Pid1_Host,
  p1.name  AS Name1_Host,
  p2.pk    AS Pid2,
  p2.name  AS Name2
FROM
             event
  INNER JOIN matchup m1 ON (event.pk = m1.e)
  INNER JOIN matchup m2 ON (event.pk = m2.e)
  INNER JOIN participant p1 ON (m1.isHost = 1 AND m1.p = p1.pk)
  INNER JOIN participant p2 ON (m2.isHost != 1 AND m2.p = p2.pk)
WHERE
  event.pk IN (SELECT e FROM matchup GROUP BY e HAVING COUNT(*) = 2)

+---------+-----------+------------+------+--------+
| EventID | Pid1_Host | Name1_Host | Pid2 | Name2  |
+---------+-----------+------------+------+--------+
|       1 |         7 | Lions      |    8 | Tigers |
|       2 |        11 | Dogs       |    9 | Cats   |
+---------+-----------+------------+------+--------+
2 rows in set (0.00 sec)


-- SELECT the events with 3 participants
SELECT
  event.pk AS EventID,
  p1.pk    AS Pid1_Host,
  p1.name  AS Name1_Host,
  p2.pk    AS Pid2,
  p2.name  AS Name2,
  p3.pk    AS Pid3,
  p3.name  AS Name3
FROM
             event
  INNER JOIN matchup m1 ON (event.pk = m1.e)
  INNER JOIN matchup m2 ON (event.pk = m2.e)
  INNER JOIN matchup m3 ON (event.pk = m3.e)
  INNER JOIN participant p1 ON (m1.isHost = 1 AND m1.p = p1.pk)
  INNER JOIN participant p2 ON (m2.isHost != 1 AND m2.p = p2.pk)
  INNER JOIN participant p3 ON (m3.isHost != 1 AND m3.p = p3.pk AND p3.pk != p2.pk)
WHERE
      p2.pk < p3.pk -- to remove spurious rearrangements of Name2 and Name3
  AND event.pk IN (SELECT e FROM matchup GROUP BY e HAVING COUNT(*) = 3)


+---------+-----------+------------+------+-------+------+-------+
| EventID | Pid1_Host | Name1_Host | Pid2 | Name2 | Pid3 | Name3 |
+---------+-----------+------------+------+-------+------+-------+
|       3 |         2 | Two        |    1 | One   |    3 | Three |
+---------+-----------+------------+------+-------+------+-------+
1 row in set (0.00 sec)