Join same field multiple times in table

2018-05-31 mysql

I have this 2 tables and I am trying to join both of them multiple times but failed. Below are the tables.

Table ccaSubjects:

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

Table rispEnrollment

+--------------------+-----------+-----------+----------+
| studentIdentifier  | firstCCA  | secondCCA | thirdCCA |
+--------------------+-----------+-----------+----------+
| [email protected]   | Robotics  | Singing   | Dancing  |
| [email protected]   | Chess     | Singing   | Robotics |
| [email protected]    | Badminton | Dancing   | Chess    |
| [email protected]  | Football  | Fitness   | Robotics |
| [email protected] | Robotics  | Singing   | Chess    |
+--------------------+-----------+-----------+----------+

I would like my output to be like:

+--------------------+-----------+-----------+----------+-----------+-----------+-----------+
| studentIdentifier  | firstCCA  | secondCCA | thirdCCA | CCA1price | CCA2price | CCA3price |
+--------------------+-----------+-----------+----------+-----------+-----------+-----------+
| [email protected]   | Robotics  | Singing   | Dancing  |      1000 |       200 |       200 |
| [email protected]   | Chess     | Singing   | Robotics |       100 |       200 |      1000 |
| [email protected]    | Badminton | Dancing   | Chess    |       300 |       200 |       100 |
| [email protected]  | Football  | Fitness   | Robotics |       250 |       600 |      1000 |
| [email protected] | Robotics  | Singing   | Chess    |      1000 |       200 |       100 |
+--------------------+-----------+-----------+----------+-----------+-----------+-----------+

From my code, I am only able to use an inner join one time and get the CCA1price, and I cannot get cca2price and cca3price anymore because the error keeps saying Same aliases.

Answers

You may join the rispEnrollment table to the ccaSubjects table as many times as you need. In this case, you may join three times to bring in the price columns for each of the three subject columns.

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;

Note that I use left joins here just in case the rispEnrollment table might have a subject which does not match to anything in the ccaSubjects table.

Related