CODEWITHSUNDEEP
sql
Alexandros Marinos
Alexandros Marinos

Reputation: 1396

Find Missing Pairs in SQL

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.

Upvotes: 4

Views: 504

Answers (2)

Imre L
Imre L

Reputation: 6249

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"

Upvotes: 7

josephj1989
josephj1989

Reputation: 9709

with Courses as(
select 1 as id,'Math' as name union all
select 2 as id,'English' as name union all
select 3 as id,'Physics' as name union all
select 4 as id,'Chemistry' as name),
Students as(
select 1 as id,'John' as name union all
select 2 as id,'Joseph' as name union all
select 3 as id,'George' as name union all
select 4 as id,'Michael' as name
),
studcrse as(
select 1 as studid, 1 as crseid union all
select 1 as studid, 2 as crseid union all
select 1 as studid, 3 as crseid union all
select 2 as studid, 3 as crseid union all
select 2 as studid, 4 as crseid union all
select 3 as studid, 1 as crseid union all
select 3 as studid, 2 as crseid union all
select 3 as studid, 4 as crseid union all
select 3 as studid, 3 as crseid union all
select 4 as studid, 4 as crseid )

SELECT A.ID AS studentId,a.name as studentname,b.id as crseid,b.name as crsename
from Students as a 
cross join
Courses as b
where not exists
(
select 1 from studcrse as c
where c.studid=a.id
and c.crseid=b.id)

Upvotes: 1

Related Questions