I have a scenario where I have to find IDs within each group which are connected to all other IDs in the same group. So basically we have to treat each group separately.

In the table below, the group A has 3 IDs 1, 2 and 3. 1 is connected to both 2 and 3, 2 is connected to both 1 and 3, but 3 is not connected to 1 and 2. So 1 and 2 should be output from group A. Similarly in group B only 5 is connected to all other IDs namely 4 and 6 within group B, so 5 should be output. Similarly from group C, that should be 8, and from group D no records should be output.

So the output of the select statement should be 1, 2, 5, 8.

I was able to do this if group level was not required, by below SQL:

SELECT ID FROM <table> 
where CONNECTED_TO in (select ID from <table>) 
group by ID
having count(*) = <number of records - 1>

But not able to find correct SQL for my scenario. Any help is appreciated.

CodePudding user response：

You may use count and count(distinct) functions as the following:

select id
from tbl T
where connected_to in 
(
  select id from tbl T2
  where T2.grp = T.grp
)
group by grp, id
having count(connected_to) = 
  (
    select count(distinct D.id) - 1
    from tbl D
    where T.grp = D.grp
  )

When count(connected_to) group by grp, id equals to the count(distinct id) - 1 with the same grp, this means that the ID is connected to all other IDs.