从表格中获取所有记录，并从其他表格中获取与其相关的信息

Question

我有三张表格（学生，团体，主题）和另外两张表格作为桥梁（弱）表格来连接学生与另外两张表格（students_groups，students_subjects）.something LIK这样的：

+-----+----------------+  +-----+-----------+ +-----+-----------+ 
| 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   | 
    |   |   |  +------------+------------+ 
    +------------+-----------+ 

我想要的是：显示所有学生（它们是否被分配到组或不，它们是否被registerd的科目或不）的主题和组。

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

我前面的查询只显示被分配到一组，并registerd一些主题

 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