CODEWITHSUNDEEP
sqloracle-databaserecursive-query
Gella
Gella

Reputation: 35

Oracle recursive sql

I have a partner1 <-> partner2 relationship where I have something like this:

(partner1, partner2) 
(1,       2)
(3,       2)
(3,       5)
(4,       5)
(4,       8)
(33,      45)

What I have is one of the partner1 value, and I need to see all the partner1 values in this chain. So, in the above example, I would have partner1=3, and I would like to see the list: 

1
3
4

The chain is unlikely to have more than 10 links.

Upvotes: 0

Views: 77

Answers (1)

MT0
MT0

Reputation: 167902

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE partners ( partner1, partner2 ) AS
  SELECT 1,2 FROM DUAL UNION ALL
  SELECT 3,2 FROM DUAL UNION ALL
  SELECT 3,5 FROM DUAL UNION ALL
  SELECT 4,5 FROM DUAL UNION ALL
  SELECT 4,8 FROM DUAL UNION ALL
  SELECT 33,45 FROM DUAL;

Query 1:

SELECT DISTINCT PARTNER1
FROM PARTNERS
START WITH partner1 = 3
CONNECT BY NOCYCLE
             ( MOD( LEVEL, 2 ) = 0 AND PRIOR partner2 = partner2 )
          OR ( MOD( LEVEL, 2 ) = 1 AND PRIOR partner1 = partner1 )

Results:

| PARTNER1 |
|----------|
|        1 |
|        4 |
|        3 |

Upvotes: 1

Related Questions