如何选择未在多个表中注册的行

Question

我有三张桌子：

学生

id, name
1, Max
2, Brian

类

id, code, type
1, AIF102, Lab
2, AIF102, Class
3, AIF104, Class

student_class

student_id, class_id
1, 1

如何选择在student_class没有注册代码的学生所在的代码行。Ie，只需选择代码AIF104，学生是最大的。选择学生所在的代码AIF102和AIF104。抱歉，以前英语不太好。

Answer 1

您可以使用“左联接”和“is null”来获取未注册的课程。

select c.*
from class c
left join student_class sc on(c.id = sc.class_id )
left join student s on (s.id = sc.student_id and s.id = 1 )
where  c.`type` = 'Class'
and sc.student_id  is null

Demo

当然，又一次对同名的尝试

select c.*
from class c
where not exists (
  select c1.code
  from class c1
  left join student_class sc on(c1.id = sc.class_id )
  where  sc.student_id  = 1
  and c1.code = c.code
)

Demo

Answer 2

你可以这样做：

SELECT * FROM student S, class C, student_class SC WHERE
S.id = SC.student_id AND SC.class_id = C.id

Answer 3

把所有的桌子连在一起。只要明确声明关系，就应该能够获得所需的记录。只需添加您自己的WHERE子句

SELECT s.name, c.code, c.type
FROM class c
     INNER JOIN student_class sc ON c.id = sc.class_id
     INNER JOIN student s ON s.id = sc.student_id