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.
All you need is to use connect_by_root
operator.
I'm not sure about query goal (e.g. to return or not to return a talented grandfather if parents are not talented) but usage of that operator may look like that:
select
originated_from_child,
found_ancestor,
is_ancestor_talented
from (
select
CONNECT_BY_ROOT relations.child originated_from_child,
relations.parent found_ancestor,
(
select count(1)
from table2
where
b = a
and
talent = 'something'
and
rownum = 1
) is_ancestor_talented
from
table1 relations
start with
relations.a in (
select talents.b
from table2 talents
where talents.talent = 'something')
connect by
prior relations.child = relations.parent
)
where
originated_from_child <> found_ancestor
and
is_ancestor_talented = 1