mysql - MySQL - 带有 3 个表的 GROUP_CONCAT 的 LEFT JOIN - 未知列

Question

我正在尝试加入 3 个表并执行 GROUP_CONCAT，但得到

Unknown column 'program_outcome.program_outcome_pk' in 'field list'

询问：

SELECT program_outcome.program_outcome_pk, 
       program_outcome.program_outcome, 
       GROUP_CONCAT(program_outcome_unit_outcome_lookup.unit_outcome_pk) unit_outcome_pks, 
       GROUP_CONCAT(unit_outcome.unit_outcome) unit_outcomes 
FROM   program_outcome po 
       LEFT JOIN program_outcome_unit_outcome_lookup pouo 
               ON po.program_outcome_pk = pouo.program_outcome_fk
       LEFT JOIN unit_outcome uo 
               ON pouo.unit_outcome_fk = uo.unit_outcome_pk 
GROUP  BY po.program_outcome_pk, 
          po.program_outcome

我正在寻找的结果如下：

+----------------------------------+--------------------------------------------------+
| program_outcome.program_outcome  |  unit_outcome.unit_outcome                       |
+----------------------------------+--------------------------------------------------+
| program outcome 1                | unit_outcome 2, unit_outcome 5                   |
| program outcome 2                | unit_outcome 7, unit_outcome 1                   |
| program outcome 3                | unit_outcome 1, unit_outcome 9, unit_outcome 11  |
+----------------------------------+--------------------------------------------------+

分贝小提琴

标签： mysqlsqlselect

Answer 1

您需要使用在您的from和条款中定义的别名join：

SELECT po.program_outcome_pk, 
       po.program_outcome, 
       GROUP_CONCAT(pouo.unit_outcome_fk) unit_outcome_pks, 
       GROUP_CONCAT(uo.unit_outcome) unit_outcomes 
FROM   program_outcome po 
       LEFT JOIN program_outcome_unit_outcome_lookup pouo 
               ON po.program_outcome_pk = pouo.program_outcome_fk
       LEFT JOIN unit_outcome uo 
               ON pouo.unit_outcome_fk = uo.unit_outcome_pk 
GROUP  BY po.program_outcome_pk, 
          po.program_outcome