NOT Exists with Inner Join

Observer 0
Viewed 35

Class_List table

Class_List_id Class_id
341 4
342 4

Lesson_CLass table

Class_List_id Lesson_id
341 22
342 22

I am trying to work out a NOT EXIST IN query, which finds all the Class_List_ids in the Class_List table that do not exist in the Lesson_CLass table where the Lesson_id is 21 and class ID is 4.

 SELECT Class_List.class_id, Class_List.Class_List_id 
 FROM Class_List WHERE NOT EXISTS (SELECT 
 Class_List.class_id, Class_List.Class_List_id, 
 Lesson_CLass.Class_List_id, Lesson_CLass.Lesson_id 
 FROM Class_List, Lesson_CLass where 
 (Class_List.Class_List_id=Lesson_CLass.Class_List_id) AND 
 (Lesson_CLass.Lesson_id=21) AND (Class_List.Class_id=4));

This results in all the Class_List records rather than the ones that don't exist.

exists mysql
1 Answers

You can use a LEFT JOIN for this purpose

 SELECT cl.class_id, cl.Class_List_id 
 FROM Class_List cl
 left join Lesson_CLass lc 
 on cl.Class_List_id = lc.Class_List_id
 and (lc.Lesson_id = 21 and cl.Class_id = 4)
 where lc.Class_List_ids is null;
userNDn20
Related