在表中多次连接同一字段

Question

我有这两个表，我试图连接他们两个多次，但失败了。下面是表格。

表ccaSubjects：

+------------+----------+
| ccaSubject | ccaPrice |
+------------+----------+
| Chess      |      100 |
| Badminton  |      300 |
| Dancing    |      200 |
| Singing    |      200 |
| Football   |      250 |
| Fitness    |      600 |
| Robotics   |     1000 |
+------------+----------+

表rispEnrollment

+--------------------+-----------+-----------+----------+
| studentIdentifier  | firstCCA  | secondCCA | thirdCCA |
+--------------------+-----------+-----------+----------+
| elly@example.com   | Robotics  | Singing   | Dancing  |
| mike@example.com   | Chess     | Singing   | Robotics |
| tom@example.com    | Badminton | Dancing   | Chess    |
| peter@example.com  | Football  | Fitness   | Robotics |
| andrew@example.com | Robotics  | Singing   | Chess    |
+--------------------+-----------+-----------+----------+

我希望我的输出如下：

+--------------------+-----------+-----------+----------+-----------+-----------+-----------+
| studentIdentifier  | firstCCA  | secondCCA | thirdCCA | CCA1price | CCA2price | CCA3price |
+--------------------+-----------+-----------+----------+-----------+-----------+-----------+
| elly@example.com   | Robotics  | Singing   | Dancing  |      1000 |       200 |       200 |
| mike@example.com   | Chess     | Singing   | Robotics |       100 |       200 |      1000 |
| tom@example.com    | Badminton | Dancing   | Chess    |       300 |       200 |       100 |
| peter@example.com  | Football  | Fitness   | Robotics |       250 |       600 |      1000 |
| andrew@example.com | Robotics  | Singing   | Chess    |      1000 |       200 |       100 |
+--------------------+-----------+-----------+----------+-----------+-----------+-----------+

从我的代码中，我只能使用一次内部连接并获取CCA1price，并且不能再获取cca2price和cca3price，因为错误一直显示为Same aliases。

Answer 1

您可以根据需要多次将rispEnrollment表连接到ccaSubjects表。在这种情况下，您可以连接三次，为三个主题列中的每一个引入价格列。

SELECT
    t1.studentIdentifier,
    t1.firstCCA,
    t1.secondCCA,
    t1.thirdCCA,
    t2.ccaPrice AS CCA1price,
    t3.ccaPrice AS CCA2price,
    t4.ccaPrice AS CCA3price
FROM rispEnrollment t1
LEFT JOIN ccaSubjects t2
    ON t1.firstCCA = t2.ccaSubject
LEFT JOIN ccaSubjects t3
    ON t1.secondCCA = t3.ccaSubject
LEFT JOIN ccaSubjects t4
    ON t1.thirdCCA = t4.ccaSubject;

请注意，我在这里使用了左连接，以防rispEnrollment表可能有一个与ccaSubjects表中的任何内容都不匹配的主题。