Problem with SubSets 1

[VickyC]

hello to all

Consider a table like this:

ID	Rank	Value
=====================
1	1	1
1	2	7
1	3	10
1	4	14
1	5	23
1	6	50
2	1	1
2	2	7
2	3	11
2	4	14
2	5	23
2	6	50
3	1	1
3	2	6
3	3	7
3	4	10
3	5	11
3	6	14
3	7	23
3	8	49
3	9	50
4	1	7
4	2	11
4	3	23
5	1	7
5	2	11
5	3	23

I want to find which IDs are SubSets of which other IDs. (just to be clear, X is a subset of Y if all of its X's members are in Y)

The output should look like:

[b]DESIRED RESULT[/b]
ID_SubSet	ID_SuperSet
===========================
   1	           1
   1	           3
   2	           2
   2	           3
   4	           3
   4	           4
   4	           5
   5	           3
   5	           4
   5	           5

So, (ID = 1) is a SubSet of itself, but also of set (ID = 3). Etc...


What's really throwing me off is that the sets have different sizes. As a start, I've tried code like below, but it has 2 flaws.

SELECT 
	t1.ID AS ID_SubSet, 
	t2.ID As ID_SuperSet
FROM 
	tbl_Data t1
INNER JOIN
	tbl_Data t2
ON
	(t1.Value = t2.Value)
GROUP BY
	t1.ID, t2.ID
HAVING
	COUNT(*) = 6;


[b]ACTUAL RESULT (INCORRECT)[/b]
ID_SubSet  ID_SuperSet
======================
    1           1 	   
    1	        3
    2           2
    2           3
    3           1
    3           2

The 1st 4 rows are correct. But, the 2 flaws are...
a) Even though they have 6 matching elements, (ID=3) is NOT a SubSet of (ID = 1), and (ID=3) is NOT a SubSet of (ID = 2)
b) There is no mention of (ID = 4) or (ID = 5)

I'm sure these problems are caused by the fact that the sets can have different numbers of elements, but I'm not really sure how to proceed.


Many thanks in advance for any clues.
Vicky C.

 

[PHV]

Perhaps this ?
HAVING COUNT(*) = (SELECT COUNT(*) FROM tbl_Data WHERE ID=t1.ID)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

hey PHV - excellent result, and in record time!
(I don't think I've ever seen a subquery in a Having clause before!)
This can also be readily modified to deal with Proper subsets as well.

Thanks much