0

i have three tables (students, groups, subjects) and two other tables as bridge(weak) table to connect students with the other two tables (students_groups , students_subjects).something lik this:

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

what i want is : to display all student (wether they are assigned to group or not, wether they are registerd for subjects or not ) with the subject and groups.

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

my earlier query shows only students that are assigned to a group and registerd for some subject 

     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       |
+-----+---------+-----------+
Chukwu Remijius
  • 323
  • 1
  • 14
george
  • 97
  • 1
  • 12

1 Answers1

1

Use LEFT OUTER JOIN instead of 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
Bharath K
  • 177
  • 1
  • 2
  • 11
  • thanks alot , i tried to find some resource about outer join things but i could not find any , do you know any website the explain it clearly , thanks again – george Jun 13 '17 at 21:39
  • I'd try one of the following sites for more info. You'll also find others if you google "mysql join". When I was learning, the Venn Diagram visualizations were pretty helpful. https://www.w3schools.com/sql/sql_join.asp https://www.techonthenet.com/mysql/joins.php – McGlothlin Jun 13 '17 at 22:08
  • If you need something quick, look into link. It is explained using venn diagrams which is easy to understand: https://stackoverflow.com/questions/406294/left-join-vs-left-outer-join-in-sql-server – Bharath K Jun 14 '17 at 22:14