Ytukyg

Let's say I have two tables 'Rel' and 'Workers'.
I want to list the names as pairs of those people who are in a transitive relationship.

So in my example 100 -> 105 and 105 -> 911 means that people with the Ids 100 and 911 are
in such a relationship. So I want their names to be listed in my result table.

I tried using joins and 'connect by' to solve this but kept failing. Hope you guys can help.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Rel ( Employee, Boss ) AS

SELECT 100, 105 FROM DUAL UNION ALL

SELECT 177, 180 FROM DUAL UNION ALL

SELECT 105, 911 FROM DUAL UNION ALL

SELECT 400, 402 FROM DUAL;



CREATE TABLE Workers ( id, name ) AS

SELECT 100, 'A' FROM DUAL UNION ALL

SELECT 105, 'B' FROM DUAL UNION ALL

SELECT 177, 'C' FROM DUAL UNION ALL

SELECT 180, 'D' FROM DUAL UNION ALL

SELECT 400, 'E' FROM DUAL UNION ALL

SELECT 402, 'F' FROM DUAL UNION ALL

SELECT 911, 'G' FROM DUAL;

Query 1:

SELECT e.name as employee,

       w.name as related_to

FROM   (

  SELECT CONNECT_BY_ROOT( Employee ) AS employee,

         boss AS related_to

  FROM   Rel

  CONNECT BY PRIOR Boss = Employee

) r

INNER JOIN Workers e

ON e.id = r.employee

INNER JOIN Workers w

ON w.id = r.related_to

Results:

| EMPLOYEE | RELATED_TO |

|----------|------------|

|        A |          B |

|        C |          D |

|        E |          F |

|        B |          G |

|        A |          G |