Table1:
Child Parent a
Bob Chris 2
Chris Kate 1
Shane Lana 3
Nala Bob 4
Table2:
b Talent
1 'something'
2 'nothing'
3 'something'
4 'nothing'
SELECT Child
FROM Table1
INNER JOIN Table2 ON (Table1.a = Table2.b)
WHERE Table2.Talent = 'something'
connect by prior Table1.Child = Table1.Parent
This code returns rows of parents
Chris
If the "Where" clause was not included, the code would return:
Bob
Chris
Kate
Chris
Kate
Shane
Lana
etc
What I want to return is the following in a row, not in columns:
Bob Chris
Where Chris is the one with a talent and is parent to Bob, so the code returns not only the parent, but also the child which originated the query for that parent, so in this code:
SELECT Child
FROM Table1
INNER JOIN Table2 ON (Table1.a = Table2.b)
WHERE Table2.Talent = 'something'
connect by prior Table1.Child = Table1.Parent
I would have the Child chris with the talent and the previous child Bob who originated the search for chris, so lets say if Bob was child to Gala and Gala was child to Chris, I would still want to get only Bob and Chris in the result.
Conditions: I do not have the permission to create temporary or any sort of tables, so I cannot use any loop to do this unless I just do not know how to do it without a temporary table
I do not know how I can return a child from before the "prior" statement and the new "child" which is actually the parent of the previous child.
You can use "HR" database to Test
SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE LEVEL > 1 and department_id = 110
CONNECT BY PRIOR employee_id = manager_id;
Employee Manager Pathlen Path
--------------- ------------ ---------- ----------------------------
Higgins Kochhar 1 /Kochhar/Higgins
Gietz Kochhar 2 /Kochhar/Higgins/Gietz
Gietz Higgins 1 /Higgins/Gietz
Higgins King 2 /King/Kochhar/Higgins
Gietz King 3 /King/Kochhar/Higgins/Gietz