Assume there's a relational database with 3 tables:
Courses {name, id},
Students {name, id},
Student_Course {student_id, course_id}
I want to write an SQL that gives me the student-course pairs that do NOT exist. If that is not feasible, at least it'd be good to know if there are missing pairs or not.
Also, since this is a small part of a larger problem I'd like to automate, seeing many different ways of doing it would be useful.
1st find all pairs and then remove pairs present (either by left join/not null
or not exists
)
select s.id as student_id, c.id as course_id
from Courses as c
cross join Students as s
left join Student_Course as sc on sc.student_id = s.id and sc.course_id = c.id
where sc.course_id is null -- any sc field defined as "not null"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With