SQL将表B中的两个或多个数据与表A中的公共数据连接起来

Question

注:已编辑

现实世界中的情况是一系列事件，每个事件都有两个或多个参与者(就像运动队一样，尽管一个事件中可以有两个以上的参与者)，其中只有一个是事件的主办方。存在用于每个唯一事件的事件db表和具有唯一参与者的参与者db表。他们使用比赛表连接在一起。它们看起来像这样：

事件

EventID (主键)

(其他事件数据，如日期等)

参与者

ParticipantID (主键)

名字

比赛

EventID (FK到事件表)

ParicipantID (FK到参与者)

主机(1或0，每个EventID仅1个主机=1个)

如果我知道有两个参与者，我希望得到的结果是这样的：

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

同样，如果我知道有三个参与者，我如何做同样的事情？

我怀疑答案是相当简单的，但出于某种原因，我不会对此感到困惑。另一方面，这也是非常困难的。:)

如果MySQL5会影响可用的SQL，我会使用MySQL5。

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)