2.17 Comparing a Set with Its Universe

2.17.1 Problem

You want to find out if a set contains all the elements of its universe. Then you want to report it as either a full or partial match. For example, assume that you want to generate a list showing which students have fulfilled all requirements with respect to the term papers they are required to turn in and which students have completed only a partial number of their term paper requirements.

2.17.2 Solution

Use the CourseMaster table defined in the previous recipe to determine the universe of required papers for each student and course, and then compare that to the actual papers submitted. Here's one approach to doing this:

SELECT s.StudentName, s.CourseId,
    CASE WHEN COUNT(*)=MAX(c.numTermPapers) 
       THEN 'All submitted'
       ELSE CONVERT(VARCHAR(8),MAX(c.numTermPapers)-COUNT(*))+' missing'
    END status
FROM Students s JOIN CourseMaster c
   ON s.CourseId=c.CourseId
GROUP BY s.StudentName, s.CourseId
ORDER BY s.StudentName, s.CourseId

This query will return results similar to the following:

StudentName  CourseId  status           
------------ --------- ---------------- 
Andrew       ACCN101   1 missing
Andrew       MGMT120   All submitted
Bert         ACCN101   1 missing
Cindy        ACCN101   2 missing
Cindy        MGMT120   All submitted

2.17.3 Discussion

The query joins the Students table and the CourseMaster table to find the required number of term papers for each course. The join is done using the CourseId column to retrieve the appropriate numTermPapers value. The results are then grouped so that there is one row for each student and course combination.

The core of the query is the CASE statement. In the set of rows for each course and student, each row represents a term paper. To find the number of term papers the student has handed in, we just need to count the rows. Then the CASE statement compares that count with the required number of papers for the course. The result from the case statement will be a message indicating whether or not all requirements have been fulfilled.

The MAX function in this query is here only for syntactic purposes. The number of term papers is dependent on the CourseId value, so there will only be one number for a given group. MAX is only necessary because numTermPapers is not a GROUP BY column. You could add numTermPapers to the column list in the GROUP BY clause — allowing you to dispense with MAX — but the extra GROUP BY column will hurt the efficiency of the query.