CODEWITHSUNDEEP
sqlsql-server
TriniTY
TriniTY

Reputation: 129

Set contains other set in SQL

Is anybody to know how to find sets which contain all sets of other sets

table

id elem
1    A
1    C
2    B 
2    D
2    A
2    C  
3    A
3    E
3    F
4    F
4    F

I want to get this:

id
2
3

In that case id 2 contains (A,C) - all set of id 1 and id 3 contains (F) - all set of id 4

In my query I need to get all id which contains all set (all elements) at least one id.
I will be very grateful. Thank you.

Upvotes: 1

Views: 2261

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

I think the following does what you want:

select t1.id
from t t1 join
     (select t.*, count(*) over (partition by id) as cnt
      from t
     ) t2
     on t1.elem = t2.elem and t1.id <> t2.id
group by t1.id, t2.id, t2.cnt
having count(*) = cnt;

This matches each id to each other id based on the elements. If the number of matches equals the count in the second set, then all match -- and you have a superset.

I notice that you have duplicate elements. Let's handle this with a CTE:

with t as (
      select distinct id, elem
      from t
     )
select t1.id
from t t1 join
     (select t.*, count(*) over (partition by id) as cnt
      from t
     ) t2
     on t1.elem = t2.elem and t1.id <> t2.id
group by t1.id, t2.id, t2.cnt
having count(*) = cnt;

Upvotes: 1

EoinS
EoinS

Reputation: 5482

I didn't have much to go off of for this one, but it was interested so I guessed. I'm using SQL Server

Follow along in Rextester

I submit the answer which has nested selects, then I follow through on the step by step logic to clarify what's going on:

Build a quick table

CREATE TABLE a (id int, t varchar(10))
INSERT INTO a (id, t)
VALUES (1,'A'),(1,'B'),(1,'B'),(1,'B'),(2,'A')
,(2,'B'),(2,'C'),(3,'C'),(3,'D'),(4,'A'),(5,'P');

Here is the solution:

select distinct p_id supersets from 
(
    select e.p_id, e.c_id, count(*) matches from (
        select distinct c.id p_id, c.t p_t, d.id c_id, d.t c_t from a c
        inner join a d on c.t = d.t and c.id <> d.id) e
    group by e.p_id, e.c_id) sup
inner join
    (select id, count(distinct t) reqs from a group by id) sub
on sub.id = sup.c_id and sup.matches = sub.reqs;

Here are the logical steps broken out to help explain why I'm doing what I'm doing:

--Step1 
--Create a list of matches between (distinct) values where IDs are not the same
select distinct c.id p_id, c.t p_t, d.id c_id, d.t c_t from a c
inner join a d on c.t = d.t and c.id <> d.id;

--Step2
--Create a unique list of parent IDs and their child IDs and the # of distinct matches
--For example 2 has 2 matches with 1 and vice versa
select e.p_id, e.c_id, count(*) matches from (
        select distinct c.id p_id, c.t p_t, d.id c_id, d.t c_t from a c
        inner join a d on c.t = d.t and c.id <> d.id) e
group by e.p_id, e.c_id;

--Step2a
--Create a sub query to see how many distinct values are in each "Set"
select id, count(distinct t) reqs from a group by id;

Now we put it all together in the join (above, first) to make sure the total # of matches from Parent to Child make up 100% of the child values ie(is a super set)

Upvotes: 1

Related Questions