从表中获取所有记录，如果存在，则从其他表中获取与之相关的信息

Question

我有三个表(学生、组、主题)和另外两个表作为桥(弱)表来连接学生和另外两个表(学生_组、学生_主题)。大概是这样的:

+-----+----------------+     +-----+-----------+    +-----+-----------+
| id  |  student_name  |     | id  |group_name |    | id  |course_name|
+-----|---------+------|     +-----|-----------+    +-----|-----------+
|  1  |  stunet 1      |     |1    | g1        |    |1    | c1        |
|  2  |  stuent 2      |     |2    | g2        |    |2    | c2        |
|  3  |  stuent 3      |     |3    |           |    |3    |           |  
+-----+---------+------+     +-----+-----------+    +-----+-----------+

    +------------+-----------+     +------------+------------+
    | student_id |  group_id |     | stuent_id  |  course_id |
    +------------+-----------+     +------------+------------+
    | 1          |  1        |     | 1          |  2         |
    | 2          |  2        |     | 3          |  1         |
    |            |           |     +------------+------------+
    +------------+-----------+

我想要的是：显示所有的学生（阉羊他们被分配到组或没有，阉羊他们是否注册科目）与主题和组。

+-----+---------+-----------+
| student | group | deleted |
+---------|-------+---------|
|student 1| g1    |c2       |
|student 2| g2    |         | 
|student 3|       |c1       |  
+---------+-------+---------+

我之前的查询只显示了分配到某个组并注册了某个科目的学生

     select students.*       
     groups.group_name,
     modules.module_name
     from students 
     JOIN students_groups ON students.id = students_groups.student_id 
     JOIN groups ON students_groups.group_id = groups.id 
     JOIN students_modules ON students.id = students_modules.student_id 
     JOIN modules ON students_modules.module_id = groups.id 

+-----+---------+-----------+
| student | group | deleted |
+---------|-------+---------|
|student 1| g1    |c2       |
+-----+---------+-----------+

Answer 1

使用LEFT OUTER JOIN而不是JOIN：

Select
std.studentname,
grp.groupname,
crs.coursename
FROM test.student std
LEFT OUTER JOIN test.stu_grp_brg sgb
on sgb.student_id=std.id
left outer join test.group grp
on sgb.group_id=grp.id
left outer join test.stu_course_brg scb
on scb.student_id=std.id
left outer join test.course crs
on crs.id=scb.course_id