mysql - LEFT OUTER JOIN to return specific data -

i have 2 tables 1 related table , other base table. first 1 class_has_student , other student. need return related data of classes particular student in class_has_student , class information classes 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 information class_has_student when pass student.id , class_has_student.class_id related data , null class_has_student.class_id if student not registered class. example if want class registration information john classes 1, 2 , 3. result expect related class student data classes 1 , 2 , class information class 3(class table not showed here, returning id sufficient). so, result must be,

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

i tried achieve through following query , several variations did not succeed. it's highly appreciated if help.

select chs.class_id, s.id   student s left outer join class_has_student chs on s.id = chs.student_id , s.id = 1001   chs.class_id in(1,2,3); 

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

this should trick.

select c.id,         student_id    class c         left join class_has_student chs                   inner join student s                           on chs.student_id = s.id                              , s.id = 1001                on c.id = chs.class_id   c.id in ( 1, 2, 3 );  

that match class has student rows students enrolled in classes, , against list of classes in order see classes , aren't enrolled in, , limit classes interested in.