Databases Reference
In-Depth Information
4
1
4
2
4
3
4
4
5
1
5
2
5
8
19.10.1 Solution
This problem has an elegant solution using a single SQL statement. While, in general,
SQL does not permit us to compare two sets directly, as in:
(SELECT Members FROM Equality WHERE Set=1) = (SELECT Members FROM
Equality WHERE _
Set=2)
it will accept such clauses if the two
SELECT
statements return a single value.
Consider now the SQL statement:
SELECT Equality.Set, E2.Set
FROM Equality INNER JOIN Equality AS E2 ON
(Equality.Member = E2.Member) And (Equality.Set < E2.Set)
GROUP BY Equality.Set, E2.Set
HAVING
((SELECT Count(Member) FROM Equality As E3 WHERE E3.Set=Equality.Set) =
(SELECT Count(Member) FROM Equality As E3 WHERE E3.Set=E2.Set))
AND
(Count(Equality.Set) =
(SELECT Count(Member) FROM Equality As E3 WHERE E3.Set=E2.Set))
The
INNER
JOIN
is on the clause:
(Equality.Member = E2.Member) And (Equality.Set < E2.Set)
The important part of this clause is the first part. It states that we want all set pairs that
have a common member. The second part prevents returning duplicate set pairs. For
instance, if sets 1 and 2 both contain the number 3, we don't want to return both pairs
[(1,2) and (2,1)].
To illustrate further, since the number 3 is in sets 1, 2, 3, and 4, the records returned for
the member 3 are as follows:
(1,2) (from member 3)
(1,3) (from member 3)
(1,4) (from member 3)
(2,3) (from member 3)
(2,4) (from member 3)
