2.14 Finding the Complement of a Set

2.14.1 Problem

You want to find the complement of a set. Given the students example being used in this chapter, you want to list the missing term papers for each student.

2.14.2 Solution

To list rows that are missing, you have to know the available set of values. For this solution, therefore, it's necessary to create a support table that we can then use to generate the universe of possible term paper values for each student in each course.

2.14.2.1 Step 1: Create the Pivot table

Since we are dealing with term paper numbers, we need a Pivot table with one numeric column. We'll use the standard Pivot table for that, as explained in the Section 1.1 recipe in Chapter 1.

It's worth mentioning here that this Pivot table is still useful even if the number of term papers required by each course is different. The key is for the number of rows in the Pivot table to match the largest number of term papers required by any course.

2.14.2.2 Step 2: Run the query

With the Pivot table in place, the following query will return the list of missing term papers:

SELECT  s.StudentName, s.CourseId, f.i TermPaper
FROM Students s, Pivot f
WHERE f.i BETWEEN 1 AND 4 
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 results returned by this query should resemble the following:

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

2.14.3 Discussion

The queries in this recipe use a Pivot table in addition to the tables that contain the actual data. We need the Pivot table, because we need to know which term paper numbers are possible and we need to generate rows for term papers that don't exist. If a term paper hasn't been handed in, it won't have a corresponding row in the Students table. The Pivot table allows us to generate a row for that missing paper, which will be included in the query's result.

The queries join the Students table to the Pivot table and groups the result by the course ID, student name, and pivot number columns. The WHERE clause in the main query restricts the join to only as many pivot records as correspond to the number of required term papers for each course.

The HAVING statement then checks to see which pivot values do not exist in the list of term papers for each particular group. If a term paper does not exist, the expression in the HAVING clause returns TRUE and the paper pivot number is reported to identify the missing term paper.

It is important to note that data in Pivot tables is not limited to integers. You can create a Pivot table with any kind of data. For example, you could build a Pivot table that contained a list of parts needed to build a product. Then you could write a query to return missing parts for all unfinished products in an assembly plant. However, when using datatypes other than integers, you cannot fill the Pivot table with a large series of values and then limit those values in the WHERE clause. When you create a Pivot table with noninteger data, you need to specify the exact list of all possible values.