Oracle recursive sql

Question

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.

stackoverflow.com/questions/tagged/oracle+recursive-query

user330315
– user330315

2015-05-13 15:42:56 +00:00
Commented May 13, 2015 at 15:42 — user330315
– user330315, Commented May 13, 2015 at 15:42
Your example is not clear - how did you get the 1,3,4 ???

Nir Alfasi
– Nir Alfasi

2015-05-13 15:44:05 +00:00
Commented May 13, 2015 at 15:44 — Nir Alfasi
– Nir Alfasi, Commented May 13, 2015 at 15:44

MT0 · Accepted Answer · 2015-05-13 16:03:37Z

1

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 |

answered May 13, 2015 at 16:03

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle recursive sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related