2.16 Finding Complements of Sets with Different Universes

2.16.1 Problem

You want to write a query that returns the complement of several sets, but each of those sets has a different universe. For example, consider that different courses each require a different number of term papers. You want to list the papers that are missing for each student in each course. Unlike the queries in the previous recipes, this query must correctly handle the different term paper requirements (the universe) of each course.

2.16.2 Solution

You first need a master table to record the number of term papers required for each course:

CREATE TABLE CourseMaster(
   CourseId CHAR(20),
   numTermPapers INTEGER
)

After creating the CourseMaster table, you need to populate it with data. The following two INSERTs specify that the ACCN101 course requires four term papers and that the MGMT120 course requires three term papers:

INSERT INTO CourseMaster VALUES('ACCN101',4)
INSERT INTO CourseMaster VALUES('MGMT120',3)

With the CourseMaster table created and populated, you can use the following query to report on term papers that have not yet been submitted:

SELECT  s.StudentName, s.CourseId, f.i TermPaper
FROM Students s, Pivot f, CourseMaster c
WHERE f.i BETWEEN 1 AND c.numTermPapers 
   AND c.CourseId=s.CourseId 
GROUP BY s.StudentName, s.CourseId, f.i
HAVING NOT EXISTS(
   SELECT * FROM Students 
   WHERE CourseId=s.CourseId AND 
      StudentName=s.StudentName AND 
      TermPaper=f.i)
ORDER BY s.StudentName

The result is a list of term papers that each student still needs to submit to pass a given course:

StudentName  CourseId  TermPaper   
------------ --------- ---------- 
Andrew       ACCN101   1
Bert         ACCN101   4
Cindy        ACCN101   3
Cindy        ACCN101   4

2.16.3 Discussion

This query is almost identical to the query presented in the earlier recipe titled "Finding the Complement of a Set." The major difference is how term paper requirements are specified. In the previous recipe, the number of term papers for each course was the same, and that value was represented by a constant:

WHERE f.i BETWEEN 1 AND 4 

For this recipe, each course has a different requirement. That requirement has to be recorded somewhere, and that's where the CourseMaster table comes into play. The first part of the query is then modified as follows:

SELECT  s.StudentName, s.CourseId, f.i TermPaper
FROM Students s, Pivot f, CourseMaster c
WHERE f.i BETWEEN 1 AND c.numTermPapers
  AND c.CourseId = s.CourseId
...

The CourseMaster table is joined to the Students table based on the CourseId column, thus giving us access to the numTermPapers column. That column contains the course-specific requirement for the number of term papers and replaces the constant used in the earlier version of the query.