CODEWITHSUNDEEP
mysqlsqlmany-to-many
Taki
Taki

Reputation: 45

SQL query in many-to-many relationship

I am struggling a problem described bellow.

Suppose there is a many-to-many relationship between students and classes, and middle table who explains which student enrolled which classes like the image.

enter image description here

Referring from this site.

I wrote a query script to get classes enrolled by the student who is corresponding to the given student id, such that

select c.Title, 
       c.Description 
from Enrollments as e 
inner join Students s on e.Student_ID = s.id 
inner join Classes c on e.Class_ID = c.id where Student_ID = ?;

However, I am struggling a problem to query the classes not enrolled by the student with given student id.

Thanks.

Upvotes: 1

Views: 280

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

I would use exists logic here:

SELECT c.Title, c.Description
FROM Classes c
WHERE NOT EXISTS (
    SELECT 1
    FROM Enrollments e
    WHERE e.Class_ID = c.id AND e.Student_ID = ?
);

Upvotes: 3

Related Questions