Oracle 10g PL/SQL Connect By Prior returning Child and Parent on same row

Question

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.

Answer 1

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  

Answer 2

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

SQLFiddle example