MySQL左联接返回不相关的记录，尽管使用的不是NULL

Question

下面的左联接查询返回在最后一个左联接上没有匹配的记录，即评估列为null，尽管我使用了IS NOT NULL查询。

应该如何更改查询以获得匹配的记录？

基本上，我希望返回根据查找表中的相关评估分组的unit_outcome记录。

左联接的DB Fiddle显示为null的记录以供评估

SELECT *
  FROM unit
  left JOIN unit_unit_outcome_lookup
    ON unit_unit_outcome_lookup.unit_fk = unit.unit_pk
  left JOIN unit_outcome
    ON unit_outcome.unit_outcome_pk = unit_unit_outcome_lookup.unit_outcome_fk
  left JOIN unit_outcome_assessment_lookup
    ON unit_outcome_assessment_lookup.unit_outcome_fk = unit_outcome.unit_outcome_pk IS NOT NULL
  left JOIN assessment
    ON assessment.assessment_pk = unit_outcome_assessment_lookup.assessment_fk IS NOT NULL
    AND unit.unit_pk ='1'

没有空记录的内连接 -如何根据评估对unit_outcomes进行分组？

Answer 1

SELECT MAX((unit.unit_pk)) AS unit_pk,
       GROUP_CONCAT(unit_outcome.unit_outcome) unit_outcomes,
       MAX(assessment.assessment) assessment,
       GROUP_CONCAT(unit_outcome.unit_outcome_pk) unit_outcome_pks, 
       assessment.assessment_pk
FROM unit
INNER JOIN unit_unit_outcome_lookup
    ON unit_unit_outcome_lookup.unit_fk = unit.unit_pk
INNER JOIN unit_outcome
    ON unit_outcome.unit_outcome_pk = unit_unit_outcome_lookup.unit_outcome_fk
INNER JOIN unit_outcome_assessment_lookup
    ON unit_outcome_assessment_lookup.unit_outcome_fk = unit_outcome.unit_outcome_pk
INNER JOIN assessment
    ON assessment.assessment_pk = unit_outcome_assessment_lookup.assessment_fk
    AND unit.unit_pk ='1'
GROUP BY assessment_pk;

小提琴

PS。详情可在对问题的评论中找到。

Answer 2

问题是这个，

unit_outcome_assessment_lookup.unit_outcome_fk = unit_outcome.unit_outcome_pk不是NULL

如果您只想选择另一个表中存在的内容，请使用内部联接或连接。让我们了解连接是如何工作的：join.asp。

还可以在表上使用别名，使查询更容易阅读。

SELECT *
  FROM unit u
  LEFT JOIN unit_unit_outcome_lookup uuol ON uuol.unit_fk = u.unit_pk
  LEFT JOIN unit_outcome uo ON uo.unit_outcome_pk = uuol.unit_outcome_fk
  JOIN unit_outcome_assessment_lookup uoal ON uoal.unit_outcome_fk = uo.unit_outcome_pk
  JOIN assessment a ON a.assessment_pk = uoal.assessment_fk
WHERE u.unit_pk ='1'