CODEWITHSUNDEEP
mysqlsql
Sam
Sam

Reputation: 1381

Is there an alternate way to write the sql query

enter image description here

I have 3 tables - class table, student table, studentclass table

From the help of these tables I need to write a query that tells us about the student who has not joined any class

I have written a query that is working, but I was wondering if there is an alternative and a simpler way to write the query to get the desired result

SELECT s.studentName, c.className 
FROM student s 
LEFT JOIN studentClass sc 
ON sc.studentId = s.studentId 
LEFT JOIN classes c 
ON sc.classId = c.classId 
WHERE c.className IS NULL

Upvotes: 0

Views: 91

Answers (1)

forpas
forpas

Reputation: 164099

Just check if the student's id exists or not in studentclass.
With NOT IN:

select * 
from student 
where studentid not in (select studentid from studentclass)

or with NOT EXISTS:

select s.* 
from student s
where not exists (
  select 1 from studentclass
  where studentid = s.studentid
)

Upvotes: 2

Related Questions