Consulta SQL para resolver dependencies transitivas en la database

Enlace SQLFIddle para estos datos.

Supongamos que tengo una tabla con la siguiente estructura:

create table rd(r1 number,r2 number, primary key (r1,r2)); 

Data de muestra:

 | R1 | R2 | ----------- | 1 | 2 | | 1 | 4 | | 2 | 3 | | 3 | 1 | | 4 | 5 | 

Lo que significa es que R1 está relacionado con R2, bidireccionalmente. Entonces, si hay una input en la database para 1,3, no habrá una input como 3,1.

De acuerdo con los datos anteriores: 1 está relacionado con 2,4,3 directamente. Y 4 está relacionado con 1 también. Entonces, a través de la dependencia transitiva, 1 y 5 también se consideran relacionados.

Resultado Esperado:

 | R1 | R2 | ----------- | 1 | 2 | | 1 | 4 | | 1 | 3 | | 1 | 5 | 

¿Alguien puede escribir una consulta SQL para esto?

Como está ejecutando Oracle 11g (y si es la versión 2), como uno de los methods, puede usar la expresión de tabla común recursiva (también conocida como factorización subconsulta recursiva) para get el resultado deseado .

 SQL> with rcte(r1, r2, lv, root) as( 2 select r1 3 , r2 4 , 0 lv 5 , r1 6 from rd 7 where r1 = 1 8 9 union all 10 11 select t.r1 12 , t.r2 13 , lv + 1 14 , q.root 15 from rd t 16 join rcte q 17 on (t.r1 = q.r2) 18 ) 19 search depth first by r1 set s 20 cycle r1 set is_cycle to 'y' default 'n' 21 22 select root 23 , r2 24 from rcte 25 where is_cycle = 'n' 26 and r2 <> root 27 ; ROOT R2 ---------- ---------- 1 2 1 3 1 4 1 5 

La siguiente consulta funcionará siempre que haya solo un nivel de valores intermedios en la tabla.

Funciona al expandir la tabla a una monodireccional, y luego combina esa tabla con los resultados de unirla a sí misma.

 WITH expanded AS (SELECT r1, r2 FROM rd UNION ALL SELECT r2 AS r1, r1 AS r2 FROM rd) SELECT * FROM (SELECT r1, r2 FROM expanded -- direct relations UNION SELECT e1.r1 AS r1, e2.r2 AS r2 -- indirect relations FROM expanded e1 INNER JOIN expanded e2 ON e1.r2 = e2.r1 -- via transitive dependency AND e2.r2 <> e1.r1) WHERE r1 = 1 ORDER BY r1, r2