Oracle SQL Is there a way to find intersections of data sets stored in same table when number of data sets are dynamic

Question

Imagine the following data set. (This is not the exact same problem I am working on, but this is just a simple example that demonstrate the requirement)

Student ID,  Course ID
S1           C1
S1           C2
S1           C3
S2           C1
S2           C3
S3           C1
S3           C2

In above data set each student is registered under number of courses. I want to find out which students are taking all the causes in this table.

So if I use INTERSECT keyword it will look like this.

SELECT student_id FROM <table> where course_id = 'C1'
INTERSECT
SELECT student_id FROM <table> where course_id = 'C2'
INTERSECT
SELECT student_id FROM <table> where course_id = 'C3'

Obviously this work only if the list of courses are fixed.

Is there a way to do this with the list of courses are dynamic and using SQL only.

I could write a PLSQL function to do this. But then there will be a cursor executed for each unique course id and intersection will be done by PLSQL code.

I am looking to see if it is possible to offload as much of it as possible to SQL engine (may be using analytical functions) as the data set involved can be large.

score 4 · Answer 1 · edited Jul 04 '19 at 12:45

Try something like this:

SELECT student_id FROM <table> 
WHERE course_id IN (SELECT course_id FROM <anothertable>) 
GROUP BY student_id 
HAVING COUNT(DISTINCT course_id)=(SELECT COUNT(DISTINCT course_id) FROM <anothertable>)

This way, you ensure that each returned student_id is registered for each course_id specified in <anothertable>.

This is called "Relational Division", see also https://www.red-gate.com/simple-talk/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

EDIT (by Gordon):

Only one table is mentioned in the question, so the query is:

SELECT student_id
FROM t
GROUP BY student_id 
HAVING COUNT(DISTINCT course_id) = (SELECT COUNT(DISTINCT course_id) FROM t)

@Rakhitha . . . I think this is a better solution. – Gordon Linoff Jul 04 '19 at 12:44 — Gordon Linoff, Jul 04 '19 at 12:44

score 3 · Accepted Answer · answered Jul 04 '19 at 05:45

Oracle allows you to use COUNT(DISTINCT) as an analytic function. So, you could do:

SELECT DISTINCT student_id 
    FROM   (SELECT student_id, 
                  COUNT(DISTINCT course_id) 
                  OVER ( PARTITION BY student_id  )  AS course_per_student, 
                  COUNT(DISTINCT course_id) OVER ()  AS available_courses 
        FROM   t) 
WHERE  course_per_student = available_courses

DEMO

Oracle SQL Is there a way to find intersections of data sets stored in same table when number of data sets are dynamic

2 Answers2