时间戳

首页 > 解决方案 > 尽管使用了 IS NOT NULL，但 MySQL Left Join 返回不相关的记录

mysql - 尽管使用了 IS NOT NULL，但 MySQL Left Join 返回不相关的记录

问题描述

以下带有左连接的查询返回在最终左连接上没有匹配项的记录，即评估列为空，尽管我的查询带有IS NOT NULL.

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

基本上我想根据查找表中的相关评估返回 unit_outcome 记录 GROUPED。

用于 LEFT JOIN 的 DB Fiddle显示用于评估的空记录

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'

没有空记录的 INNER JOIN的 DB Fiddle - 如何通过评估对 unit_outcomes 进行分组？

标签： mysql

解决方案

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。详细信息可以在问题的评论中找到。

推荐阅读