GROUP_CONCAT and JOINS with multiple lookup tables

I have a simple series of joins with a GROUP_CONCAT that need to be performed on a number of tables and lookup tables.

    Table unit:
    +---------+-----------+
    | unit_pk | unit_name |
    +---------+-----------+

    Table unit_outcome:
    +----------------+--------------+
    |unit_outcome_pk | unit_outcome |
    +----------------+--------------+
    
    Table discipline_outcome:
    +----------------------+--------------------+
    |discipline_outcome_pk | discipline_outcome |
    +----------------------+--------------------+
    
    Table unit_outcome_discipline_outcome_lookup:
    +-------------------------------------------+-----------------+-----------------------+
    | unit_outcome_discipline_outcome_lookup_pk | unit_outcome_fk | discipline_outcome_fk |
    +-------------------------------------------+-----------------+-----------------------+

    Table unit_unit_outcome_lookup:
    +----------------------------+-----------------+---------+
    |unit_unit_outcome_lookup_pk | unit_outcome_fk | unit_fk |
    +----------------------------+-----------------+---------+

So, if unit_fk = 1 and discipline_fk = 5 in the corresponding lookup tables, the results should look like the following given the test data in the fiddle (link below):

+---------------+-----------------------+
|unit_outcome 1 | discipline_outcome 10 |
|               |                       |
+---------------+-----------------------+
|unit_outcome 2 | discipline_outcome 7  |
|               |                       |
+---------------+-----------------------+
|unit_outcome 3 | discipline_outcome 1  |
|                 discipline_outcome 8  |
|               | discipline_outcome 9  |
+---------------+-----------------------+

What I have so far is the following query, which just gets results for unit_outcome -> discipline_outcomes, without filtering on the unit and the unit_unit_outcome_lookup tables, e.g. for unit_fk = '1' as well as the discipline_fk:

SELECT unit_outcome, 
GROUP_CONCAT(discipline_outcome SEPARATOR '|') AS discipline_outcomes
FROM unit_outcome u
LEFT JOIN unit_outcome_discipline_outcome_lookup l ON u.unit_outcome_pk = l.unit_outcome_fk
LEFT JOIN discipline_outcome d ON l.discipline_outcome_fk = d.discipline_outcome_pk
WHERE d.discipline_fk = '5' 
GROUP BY unit_outcome

I need to incorporate those two tables in the above query to only have unit_outcomes that correspond to a given unit_fk as well as the discipline_fk. Not sure how to do this though.

db fiddle

Answer

You must join all the tables with INNER joins like this:

SELECT u.unit_outcome, 
  GROUP_CONCAT(d.discipline_outcome ORDER BY d.discipline_outcome_pk SEPARATOR '|') AS discipline_outcomes
FROM unit_outcome u
INNER JOIN unit_outcome_discipline_outcome_lookup l ON u.unit_outcome_pk = l.unit_outcome_fk
INNER JOIN discipline_outcome d ON l.discipline_outcome_fk = d.discipline_outcome_pk
INNER JOIN unit_unit_outcome_lookup uu ON uu.unit_outcome_fk = u.unit_outcome_pk
WHERE d.discipline_fk = '5' AND uu.unit_fk = '1'
GROUP BY u.unit_outcome

Advertisement

Answer