Oracle Sq Identify Siblings Via Siblings

I have linked together groups of records which are related via a shared parent. Unfortunately there are some quite complicated family groups, and it's become clear that just using

Solution 1:

Its unclear whether the relationships are reflexive (i.e. if B is a "sibling" of A then A is a "sibling" of B) as you have some duplicate rows with the reversed relationships in your data and some where this property is not evident.

Assuming that your relationships are not reflexive then:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATETABLE A ( ID, SIBS ) ASSELECT'A', 'B'FROM DUAL UNIONALLSELECT'A', 'C'FROM DUAL UNIONALLSELECT'B', 'A'FROM DUAL UNIONALLSELECT'C', 'A'FROM DUAL UNIONALLSELECT'C', 'D'FROM DUAL UNIONALLSELECT'D', 'C'FROM DUAL UNIONALLSELECT'E', 'F'FROM DUAL UNIONALLSELECT'F', 'G'FROM DUAL UNIONALLSELECT'G', 'H'FROM DUAL;

Query 1:

SELECTDISTINCT
       CONNECT_BY_ROOT( ID ) AS ID,
       SIBS
FROM   A
WHERE  CONNECT_BY_ROOT( ID ) <> SIBS
CONNECT BY NOCYCLE
       PRIOR SIBS = ID
ORDERBY ID, SIBS

Results:

| ID | SIBS |
|----|------|
|  A |    B |
|  A |    C |
|  A |    D |
|  B |    A |
|  B |    C |
|  B |    D |
|  C |    A |
|  C |    B |
|  C |    D |
|  D |    A |
|  D |    B |
|  D |    C |
|  E |    F |
|  E |    G |
|  E |    H |
|  F |    G |
|  F |    H |
|  G |    H |

Query 2: If they are reflexive then you can use UNION [ALL] to duplicate the table with the relationships in the reverse direction and then use the previous technique:

SELECTDISTINCT
       CONNECT_BY_ROOT( ID ) AS ID,
       SIBS
FROM   (
  SELECT ID, SIBS FROM A
  UNION
  SELECT SIBS, ID FROM A
)
WHERE  CONNECT_BY_ROOT( ID ) <> SIBS
CONNECT BY NOCYCLE
       PRIOR SIBS = ID
ORDERBY ID, SIBS

Results:

| ID | SIBS |
|----|------|
|  A |    B |
|  A |    C |
|  A |    D |
|  B |    A |
|  B |    C |
|  B |    D |
|  C |    A |
|  C |    B |
|  C |    D |
|  D |    A |
|  D |    B |
|  D |    C |
|  E |    F |
|  E |    G |
|  E |    H |
|  F |    E |
|  F |    G |
|  F |    H |
|  G |    E |
|  G |    F |
|  G |    H |
|  H |    E |
|  H |    F |
|  H |    G |

Solution 2:

As an alternative to a hierarchical query, you coudl also use recursive subquery factoring:

with r (pno, sibs) as (
  select a.id, a.sibs
  from a
  unionallselect r.pno, a.sibs
  from r
  join a on a.id = r.sibs
)
cycle pno, sibs set is_cycle to1default0selectdistinct pno, sibs
from r
where pno != sibs
orderby pno, sibs;

PNO SIBS
--- ----
A   B   
A   C   
A   D   
B   A   
B   C   
B   D   
C   A   
C   B   
C   D   
D   A   
D   B   
D   C   

The anchor member gets the raw data from your table. The recursive member joins each row found so far back to your main table, keeping the original pno (as the equivalent to connect_by_root(id)).

The hierarchical query is likely to perform better, I think, but it depends a bit on your data, so you could try both approaches.