Mysql LEFT JOIN with IS NULL 返回空结果

Question

我正在尝试选择不在表中的行。

简而言之，有五个表student，cls，sec，sch，std_sch。在这里，我想从std_sch中选择不在sch中的行 但是带有IS NULL的左连接返回空结果。

表cls- 类列表

id  |   ttl
===========
1   |   One
2   |   Two

表sec- 章节列表

id  |   ttl
===========
1   |   A
2   |   B

表sch——奖学金清单

id  |   ttl
===============
1   |   First
2   |   Second
3   |   Third

表student

id  |   ttl |   cls |   sec
===========================
1   |   John|   1   |   1
2   |   Paul|   1   |   0

表sdt_sch- 分配给学生的奖学金清单

id  |   s_id|   sdt_sch
=======================
1   |   1   |   1

Mysql 代码

SELECT
student.id AS sid, 
student.ttl AS stdt, 
cls.ttl AS cls,
sec.ttl AS sec, 
GROUP_CONCAT(sch.ttl) AS sch 
FROM 
student
JOIN
cls ON cls.id=student.cls 
LEFT JOIN
sec ON sec.id=student.sec
LEFT JOIN
std_sch ON std_sch.s_id = student.id
LEFT JOIN 
sch ON sch.id = std_sch.sch_id
WHERE
cls.id = 1
AND
std_sch.sch_id IS NULL
GROUP BY
student.id 

预期结果应如下：因为表sdt_sch中存在第一奖学金(sch - 1(。但是我得到的关于这一行的结果为空

sid |   stdt|   cls |   sec|    sch
============================================
1   |   John|   One |   A   |   Second,Third
2   |   Paul|   One |   A   |   First,Second,Third

我已经附加了- SQL 小提琴也

Answer 1

下面一个怎么样

SELECT
student.id AS sid, 
student.ttl AS stdt, 
cls.ttl AS cls,
sec.ttl AS sec, 
GROUP_CONCAT(sch.ttl) AS sch 
FROM 
student
inner JOIN
cls ON cls.id=student.cls 
LEFT JOIN
sec ON sec.id=student.sec
left JOIN
std_sch ON std_sch.s_id = student.id 
Left JOIN 
sch ON sch.id != std_sch.sch_id or std_sch.sch_id is null
WHERE
cls.id = 1
GROUP BY student.id;

小提琴：这里

这个想法是找到未分配给学生的学者，因此在 sch 上进行左连接将起作用

Left JOIN 
sch ON sch.id != std_sch.sch_id or std_sch.sch_id is null