I have to find a subset-superset relation within a same table.  The
requirement is pretty close to parent-child relation but not exactly.
I have a table, say myTable ( ID, Content)
myTable
1  A
2  A
2  B         ie. 1 contains A
3  A             2 contains A,B
3  B             3 contains A, B, C , E.......
3  C
3  E
4  B
4  C
......

I have to create a tabe say Answer_Table ( Superset, Subset) something
like
2 1
3 1 
3 2 
3 4 
.....
and so on. 
The answer seems to demand constructing a tree, but I will take any
other situation which will give me the solution.
The answer has to be a single INSERT INTO ( may be nested/complex) for
SQL7.0

Thanks
-Abhinav

---

Request for Question Clarification by hammer-ga on 07 Mar 2003 11:08 PST

You're not going to get this with a single INSERT INTO statement. You
will need to loop through myTable, running the INSERT INTO for each
record. This can be done in one shot using a Stored Procedure. Will
you accept a Stored Procedure as an answer?

- Hammer

---

Request for Question Clarification by hammer-ga on 07 Mar 2003 11:23 PST

Hmmmm, unless something can be done with a self-join...

I'm sorry to be waffling. The second option just occured to me. I'd
still like to know if you will accept a Stored Procedure, but I'll
take a look at the self-joins also.

- Hammer

---

Clarification of Question by theabhinav-ga on 07 Mar 2003 13:50 PST

Given the nine element Table, the answer should be something like this
Answer_Table 
 
SuperSet  Subset 
 2          1 
 3          1 
 3          2 
 3          4 
 
This means that EVERYTHING contained within '1' is contained within
'2', but not necessarily EVERYTHING contained within '2' is contained
in '1'.
Its a mathematical superset/subset relation. 
 
I cannont accept stored procedure as an option. Self-joins are an
option but I havent had any luck with it yet. But I do need a single
INSERT INTO statement maybe containing nested joins etc. within it.
 
Hope it helps.

Hi, theabhinav-ga:

As noted in my earlier comment, it appears you would like to suppress
the trivial cases of a "set" containing itself, e.g. do not return any
entries paired with themselves.

I developed and tested the following "select...into" statement based
on a self-join, a possible line of solution suggested by Hammer-ga and
yourself:

SELECT A.fldInteger Superset, B.fldInteger Subset
INTO tblAnswers
FROM tblAbhinav A, tblAbhinav B
WHERE A.fldInteger <> B.fldInteger
  AND A.fldCharctr = B.fldCharctr
GROUP BY A.fldInteger, B.fldInteger
HAVING NOT EXISTS (
    SELECT * FROM tblAbhinav C
    WHERE C.fldInteger = B.fldInteger
      AND NOT EXISTS (
        SELECT * FROM tblAbhinav D
        WHERE D.fldInteger = A.fldInteger
          AND D.fldCharctr = C.fldCharctr
      )
)

The above created the expected 4 row table, after the following setup
statements had been applied to build a 9 row sample "input":

CREATE TABLE tblAbhinav (
   fldInteger int,
   fldCharctr char(1)
 PRIMARY KEY (fldInteger, fldCharctr)
)
go

INSERT into tblAbhinav VALUES
(1,'A')
INSERT into tblAbhinav VALUES
(2,'A')
INSERT into tblAbhinav VALUES
(2,'B')
INSERT into tblAbhinav VALUES
(3,'A') 
INSERT into tblAbhinav VALUES
(3,'B')
INSERT into tblAbhinav VALUES
(3,'C')
INSERT into tblAbhinav VALUES
(3,'E')
INSERT into tblAbhinav VALUES
(4,'B')
INSERT into tblAbhinav VALUES
(4,'C')
go

The SQL query "starts" by setting up an "aggregate" query (GROUP BY)
one a self-join that selects candidate pairs of unequal "grouping
numbers" (fldInteger) which "share" at least one common value (for
fldCharctr).  A little thought shows that this is without loss of
generality.  We must omit empty groups (although mathematically they
would be considered subsets) because such groups make no appearance in
the table at all.  Using GROUP BY on the self-join guarantees that
pairings will be distinct.

The HAVING clause then does the real work of the query by knocking out
any pairs A.fldInteger and B.fldInteger such that some value of
fldCharctr associated with the latter is not also associated with the
former.  Again a little thought is needed to confirm this produces the
intended effect.

regards, mathtalk-ga

Great Answer, totally works for me. I was able to reach solution
myself but yours was much simpler than mine.
Here's mine:
SELECT DISTINCT ID INTO S FROM myTable
CREATE UNIQUE CLUSTERED INDEX S_S ON S(id)
SELECT DISTINCT content INTO C FROM myTable
CREATE UNIQUE CLUSTERED INDEX C_C ON C(content)

SELECT S2.id AS SuperSet, S1.id AS Subset
FROM S S1 CROSS JOIN S S2 CROSS JOIN C 
LEFT OUTER JOIN myTABLE A
ON S1.id = A.id AND C.content = A.content
LEFT OUTER JOIN mytable B
ON S2.id = B.id AND C.content = B.content
WHERE S1.id <> S2.id
GROUP BY S1.id, S2.id
HAVING COUNT(A.content) = COUNT(A.content+B.content)
AND COUNT(A.id) < COUNT(B.id)

I am an SQL DBA, but I want to make sure I understand your needs. So
given your source table, you want a 2 column result set which lists
for each column1 in the source table, all the column1 records with the
same value in column2?

Do you only want unique records i.e. no duplicates?
3, 2 <- because "2, A" and "3, A" 
3, 2 <- because "2, B" and "3, B" 

or should I just show ONE occurance?

If possible show me your entire desired result set, given that 9 row
source table. If I understand, it would be:

1  A 
2  A 
2  B         ie. 1 contains A 
3  A             2 contains A,B 
3  B             3 contains A, B, C , E....... 
3  C 
3  E 
4  B 
4  C
 
2, 1 <- because "1, A" and "2, A"
2, 3 <- because "2, A" and "3, A" also because "2, B" and "3,B"
2, 4 <- because "2, B" and "4, B"
3, 1 <- because "1, A" and "3, A"
3, 2 <etc.
3, 4
4, 2
4, 3 

Do I understand?

Given the nine element Table, the answer should be something like this
Answer_Table

SuperSet  Subset
 2          1
 3          1
 3          2
 3          4

This means that EVERYTHING contained within '1' is contained within
'2', but not necessarily EVERYTHING contained within '2' is contained
in '1'.
Its a mathematical superset/subset relation.

I cannont accept stored procedure as an option. Self-joins are an
option but I havent had any luck with it yet. But I do need a single
INSERT INTO statement maybe containing nested joins etc. within it.

Hope it helps.

Hi, theabhinav-ga:

I infer from your examples that you intend to exclude the trivial
"subset" relations ("1" contains "1", "2" contains "2", etc.).

regards, mathtalk

Just a thought, will this work:

Select A.col1, B.col1
(select col1, col2
from mytable ) as A,
(select col1, col2
from mytable ) as B
Where A.col2 = B.col2
And A.col1 <> B.col2

Where Col1 = 1,2,3,...
Col2 = A, B, C, ...

Once again, Mathtalk, great job!!!

- Hammer