CODEWITHSUNDEEP
mysqlsql
george
george

Reputation: 97

get all record from a table, and the info related to it from other tables if exists

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

Upvotes: 0

Views: 25

Answers (1)

Bharath K
Bharath K

Reputation: 197

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

Upvotes: 1

Related Questions