Intersection of N sets

Imagine a table that enumerates membership of items to some set:
create table sets
(
setId int not null,
itemId int not null
);
some data:
set 1 = {1,3,5,7,9}
set 2 = {1,2,3,4,5}
set 3 = {4,5,6,7}
translated to this model:
setId, itemId
1,1
1,3
1,5
1,7
1,9
2,1
2,2
2,3
2,4
2,5
3,4
3,5
3,6
3,7
Consider another table containing an enumeration of sets to calculate the
intersection of:
create table setIntersection
(
setId int not null
);
If the setIntersection table contains the records {1,2,3}, I'd like to calcu
late
the intersection of the items contained in the sets 1, 2 and 3.
In the example above, this would be the {5}.
While this is trivial to do in a typical imperative fashion (looping inside
an SP), I'm wondering if it's possible to perform this operation in a single
query, perhaps using an CTE.Essentially, it would be a join on the the tables, each representing a set.
SELECT t1.itemid -- or t2.itemid or t3.itemid
FROM tbl t1,
tbl t2,
tbl t3
WHERE t1.itemid = t2.itemid
AND t2.itemid = t3.itemid
AND t1.setid = 1
AND t2.setid = 2
AND t3.setid = 3 ;
Anith|||Taras Tielkes (taras.tielkes@.gmail.com) writes:
> Imagine a table that enumerates membership of items to some set:
> create table sets
> (
> setId int not null,
> itemId int not null
> );
> some data:
> set 1 = {1,3,5,7,9}
> set 2 = {1,2,3,4,5}
> set 3 = {4,5,6,7}
>...
> Consider another table containing an enumeration of sets to calculate the
> intersection of:
> create table setIntersection
> (
> setId int not null
> );
> If the setIntersection table contains the records {1,2,3}, I'd like to
> calculate the intersection of the items contained in the sets 1, 2 and
> 3. In the example above, this would be the {5}.
SELECT itemId
FROM sets s
WHERE EXISTS (SELECT *
FROM setIntersection sI
WHERE sI.setId = s.setId)
GROUP BY itemId
HAVING COUNT(*) = (SELECT COUNT(*) FROM setIntersection)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ah, but I meant to ask the question in a generic way: "how can I writer a
query that will perform the required operation for an arbitrary number of
sets in the example model. Say 100 defined sets :-)

> Essentially, it would be a join on the the tables, each representing a
> set.
> SELECT t1.itemid -- or t2.itemid or t3.itemid
> FROM tbl t1,
> tbl t2,
> tbl t3
> WHERE t1.itemid = t2.itemid
> AND t2.itemid = t3.itemid
> AND t1.setid = 1
> AND t2.setid = 2
> AND t3.setid = 3 ;|||In that case, Erland's response should help. You may also want to search the
archives for "relational division" to find some related examples.
Anith|||Taras Tielkes wrote:
> Imagine a table that enumerates membership of items to some set:
> create table sets
> (
> setId int not null,
> itemId int not null
> );
> some data:
> set 1 = {1,3,5,7,9}
> set 2 = {1,2,3,4,5}
> set 3 = {4,5,6,7}
> translated to this model:
> setId, itemId
> 1,1
> 1,3
> 1,5
> 1,7
> 1,9
> 2,1
> 2,2
> 2,3
> 2,4
> 2,5
> 3,4
> 3,5
> 3,6
> 3,7
> Consider another table containing an enumeration of sets to calculate the
> intersection of:
> create table setIntersection
> (
> setId int not null
> );
> If the setIntersection table contains the records {1,2,3}, I'd like to cal
culate
> the intersection of the items contained in the sets 1, 2 and 3.
> In the example above, this would be the {5}.
> While this is trivial to do in a typical imperative fashion (looping insid
e
> an SP), I'm wondering if it's possible to perform this operation in a sing
le
> query, perhaps using an CTE.
It's relational division:
sets/setIntersection