2.6 Implementing Full Intersection

2.6.1 Problem

You have a set of sets, and you want to find the full intersection between them. Continuing with the students example, you want to list the term papers that have been handed in by all students.

2.6.2 Solution

One solution is to count the students, count the number of times each term paper has been submitted, and return only those term papers where the two counts are equal. For example:

SELECT CourseId, TermPaper 
FROM Students
GROUP BY TermPaper, CourseId
HAVING COUNT(*)=(SELECT COUNT(DISTINCT StudentName) FROM Students)

When run against the sample data for this chapter, this query will return the following result:

CourseId             TermPaper   
-------------------- ----------- 
ACCN101              2

2.6.3 Discussion

The query might look a bit strange at first. However, it follows the logic introduced in the previous recipe. The sets in the problem are groups of term papers identified by student and course IDs. In other words, we are dealing with the different sets of term papers turned in by each student. Contrary to what your initial instincts might be, we do not group the term papers into sets by students; rather, we group them into sets by paper. The GROUP BY CourseId, TermPaper clause does this for us.

The idea is that for each term paper, we use COUNT(*) in the HAVING clause to count the number of submissions. Then we compare this number to the total count of students in the table. If there are as many students in the table as there are term papers of one kind, all students have handed in the term paper and can be included in the query's result set.

Please note the DISTINCT clause in the second SELECT. It's a common mistake to forget that. That would be a mistake because then the inner SELECT would count all rows in the table instead of counting the number of distinct students.