I am working with Oracle DB, but this question really applies to a lot of generalized SQL recursion. I am attempting to recurse through a complex set of joins where I start with a list of values that exist in one of the columns. I am getting a 'structure' of values with these values being the starting point of the drill down. Below is a partial example of the query showing the connect by statement I'm currently using.
--sql continues
CONNECT BY
PRIOR COMP.COUPON=HEAD.PART
AND PRIOR COMP.COUPON=HEAD.BUSINESS_NUM
START WITH
HEAD.BUSINESS_NUM ='123'
AND HEAD.PART IN ('2928273','287632','128273')
) COMP
--sql continues
I have the query working and get the data I want. However, it would help me tremendously if I had another column that contained which of the 3 PARTS each row started with. Due to the large amount of data in the source tables I'm querying from, it is necessary to specify the values you need to use recursion on.
For another example, if I wanted to use recursion on a large organizational chart for who reports to the bosses John, Paul, Ringo, and George. Not just their direct reports, but every single person who eventually flows up to each one of them. I want a single column that specifies which one of them the recursion started with.
In my attempts so far, I haven't been able to figure out a way to "define" which value I'm starting with properly. Any help would be very much appreciated. I apologize I cannot provide the full query for security purposes nor the data I'm sourcing from. It weakens specific help for my issue, but hopefully my description for what I need makes it more clear.
I haven't been able to really specify a specific attempt at solving this issue. I've attempting some crazy joins that blow up my results and obviously don't work. I'm still learning the finer details of SQL and this issue has stumped me.
Use CONNECT_BY_ROOT:
SELECT CONNECT_BY_ROOT parent AS root_parent,
parent,
child,
SYS_CONNECT_BY_PATH(parent, '>') || '>' || child AS path
FROM table_name
START WITH parent IN (1, 2, 3)
CONNECT BY
PRIOR child = parent
Which, for the sample data:
CREATE TABLE table_name (parent, child) AS
SELECT 1, 5 FROM DUAL UNION ALL
SELECT 2, 3 FROM DUAL UNION ALL
SELECT 3, 4 FROM DUAL UNION ALL
SELECT 4, 6 FROM DUAL UNION ALL
SELECT 5, 7 FROM DUAL;
Outputs:
| ROOT_PARENT | PARENT | CHILD | PATH |
|---|---|---|---|
| 1 | 1 | 5 | >1>5 |
| 1 | 5 | 7 | >1>5>7 |
| 2 | 2 | 3 | >2>3 |
| 2 | 3 | 4 | >2>3>4 |
| 2 | 4 | 6 | >2>3>4>6 |
| 3 | 3 | 4 | >3>4 |
| 3 | 4 | 6 | >3>4>6 |