CODEWITHSUNDEEP
sqldatabaserelational-division
canoe
canoe

Reputation: 1293

Get courses chosen by all students

Now there are two tables student(sid, name) and course(cid, name), which relation is many-to-many. So there is another table student_course(sid, cid) that stores the information about what courses have been chosen by whom.

How to write the sql that can get the courses that have been chosen by all the students?

Upvotes: 0

Views: 128

Answers (2)

wildplasser
wildplasser

Reputation: 44250

Standard solution: use the NOT EXISTS ( ... NOT EXISTS (...)) construct:

  • find all courses in which all students take part
  • ==>> there must not exist a student that does not take part in this course
SELECT * FROM course c
WHERE NOT EXISTS (
        SELECT * from student s
        WHERE NOT EXISTS (
                SELECT * from student_course cs
                WHERE cs.sid = s.sid
                AND cs.cid = c.cid
                )
        )
        ;

This query is often faster (given appropiate indexes) than the count() == count() variant. The reason for this: you do not have to count all the (distinct) records; once you found one student that does not take this course you can omit this course from your list of suspects. Also: ANTI-joins often can make use of indexes [so can a count(), but that still has to count all the (distinct) keyvalues in the index]

Upvotes: 2

pinkfloydx33
pinkfloydx33

Reputation: 12739

Select c.cid, c.name 
From course c where 
(select count(1) from student) = (select count(1) from student_course sc where sc.cid = c.cid);

See SQL Fiddle

It finds all courses where the count of entries for that course in the student_course table matches the number of students

CID NAME
1   Test Course1
4   Test Course4

Upvotes: 1

Related Questions