LEFT OUTER JOIN to return specific data

Question

I have 2 tables where one is a related table and the other is a base table. First one is class_has_student and the other is student. I need to return all related data of classes for a particular student in class_has_student and class information for classes where student not registered even. (student.id references class_has_student.student_id)

  class_has_student table        student table

  class_id | student_id               id | name 
  ---------|-----------          --------|---------     
          1|       1001              1001| John
          2|       1001              1002| Michael
          1|       1002              1003| Anne
          3|       1002
          1|       1003
          2|       1003
          3|       1003
          4|       1003

I need to get information from class_has_student when I pass a student.id and class_has_student.class_id for related data and null for class_has_student.class_id if the student not registered to that class. For example if I want to get class registration information for John for classes 1, 2 and 3. The result I expect is related class student data for classes 1 and 2 and only class information for class 3(class table is not showed here, so returning id is sufficient). So, the result must be,

  class_id | student_id
  ---------|-----------
          1|       1001
          2|       1001
          3|       NULL

I tried to achieve this through following query and several variations but did not succeed. It's highly appreciated if somebody could help.

SELECT chs.class_id, s.id
  FROM student s LEFT OUTER JOIN class_has_student chs ON s.id = chs.student_id AND s.id = 1001
  WHERE chs.class_id IN(1,2,3);

The fiddle is here. http://sqlfiddle.com/#!9/839fe/4

Perhaps http://sqlfiddle.com/#!9/839fe/42 – Abhik Chakraborty Mar 28 '15 at 11:28 — Abhik Chakraborty, Mar 28 '15 at 11:28

pala_ · Accepted Answer · 2015-03-28T12:51:05.150

0

This should do the trick.

SELECT c.id, 
       student_id 
FROM   class c 
       LEFT JOIN class_has_student chs 
                 INNER JOIN student s 
                         ON chs.student_id = s.id 
                            AND s.id = 1001 
              ON c.id = chs.class_id 
WHERE  c.id IN ( 1, 2, 3 );

That will match up the class has student rows to the students that ARE enrolled in classes, and then against the list of ALL classes in order to see what classes they are AND aren't enrolled in, and then limit it to just the classes you are interested in.

edited Mar 28 '15 at 12:51

answered Mar 28 '15 at 11:33

pala_

8,901
1
15
32

Thanks for the answer. But it returns data for all classes. I require it to return only for specified classes. In this case only 1, 2, 3. – chandimak Mar 28 '15 at 11:38
Thank you very much. That works. Can't this be achieved only using class_has_student and student only and show chs.class_id. If not could you explain why (knowlege purposes only. no worries.). – chandimak Mar 28 '15 at 11:56
You have to abuse group by in order to do it with the two tables, as others have done as above. This is bad because without an aggregate function the value chosen for student_id may be indeterminate. See this other question for a more in depth explanation http://stackoverflow.com/questions/1591909/group-by-behavior-when-no-aggregate-functions-are-present-in-the-select-clause. Btw if you like the answer can you accept it? :) – pala_ Mar 28 '15 at 12:07

score 0 · Answer 2 · answered Mar 28 '15 at 12:00

SELECT c.* 
  FROM class c 
  LEFT 
  JOIN 
     ( SELECT cs.class_id 
         FROM class_has_student cs 
         JOIN student s 
           ON s.id = cs.student_id 
          AND s.name = 'John'
     ) x 
    ON x.class_id = c.id 
 WHERE c.id IN (1,2,3);

or someting like that

LEFT OUTER JOIN to return specific data

2 Answers2