CODEWITHSUNDEEP
sql-servert-sql
user2819566
user2819566

Reputation: 25

TSQL: query to select supersets and subsets pairs

given table with fields and their handlers:

Field   Handler
----------------
"1"     "A"
"1"     "B"
"2"     "A"
"2"     "B"
"2"     "C"
"3"     "C"
"3"     "D"

Each field may have many handlers. I need to write a query to select fields that have super-sets and sub-sets of handlers. For instance, as field "1" has handlers "A" and "B", and field "2" has superset of handlers ("A", "B" and "C"), I need query that shows me that:

SuperSet   SubSet
------------------
"2"        "1"
"3"        NULL

Upvotes: 1

Views: 472

Answers (1)

Rich Benner
Rich Benner

Reputation: 8113

Please don't tell anybody I wrote this code, it's very hacky.

Test Data

CREATE TABLE #TestData (Field int, Handler varchar(10))
INSERT INTO #TestData (Field, Handler)
VALUES
 (1,'A')
,(1,'B')
,(2,'A')
,(2,'B')
,(2,'C')
,(3,'C')
,(3,'D')

Query

SELECT
a.Superset
,a.SubSet
FROM
(SELECT
a.Field SuperSet
,b.Field SubSet
FROM
(
SELECT
a.Field
,    STUFF((SELECT ', ' + b.Handler
           FROM #TestData b 
           WHERE b.Field = a.Field 
          FOR XML PATH('')), 1, 2, '') Result
FROM #TestData a
GROUP BY a.Field
) a
LEFT JOIN
(
SELECT
a.Field
,    STUFF((SELECT ', ' + b.Handler
           FROM #TestData b 
           WHERE b.Field = a.Field 
          FOR XML PATH('')), 1, 2, '') Result
FROM #TestData a
GROUP BY a.Field
) b
ON a.Result LIKE '%' + b.Result + '%'
AND b.Field <> a.Field
) a
LEFT JOIN
(
SELECT
a.Field SuperSet
,b.Field SubSet
FROM
(
SELECT
a.Field
,    STUFF((SELECT ', ' + b.Handler
           FROM #TestData b 
           WHERE b.Field = a.Field 
          FOR XML PATH('')), 1, 2, '') Result
FROM #TestData a
GROUP BY a.Field
) a
LEFT JOIN
(
SELECT
a.Field
,    STUFF((SELECT ', ' + b.Handler
           FROM #TestData b 
           WHERE b.Field = a.Field 
          FOR XML PATH('')), 1, 2, '') Result
FROM #TestData a
GROUP BY a.Field
) b
ON a.Result LIKE '%' + b.Result + '%'
AND b.Field <> a.Field
) b 
ON a.SuperSet = b.SubSet
WHERE b.SubSet IS NULL

Result

Superset    SubSet
2           1
3           NULL

Upvotes: 1

Related Questions