2.16 Finding Complements of Sets with Different Universes2.16.1 ProblemYou 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 SolutionYou 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 DiscussionThis 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. |