CODEWITHSUNDEEP
sqloracle-database
carlmango11
carlmango11

Reputation: 525

Oracle SQL - join to top level record in hierarchy

I have a table called Case which has an attribute parent_case that creates a hierarchy of cases.

I'm pulling up a list of tasks which all have an association with a case. However, the case could be at any level in the hierarchy. I need to join with the top-level case because the top-level case has an association to a user table that I need to join with. (Basically I want the name of the person related to the 'original' case)

I was doing something like this:

SELECT id FROM (
  SELECT id
  FROM CASES
  START WITH id = <<key_of_case_from_task_here>>
  CONNECT BY NOCYCLE PRIOR parent_case = id
  ORDER BY datecreated ASC
) WHERE ROWNUM = 1

This builds a table where each row is a case in the hierarchy starting at the id I supply (which in the full query would be the foreign key on the task that indicates it's associated case). It then sorts by datecreated which puts my top-most case at the top (the parent is always created first) and then grabs the first one.

I was hoping the JOIN into this by replacing the <key_of_case_from_task_here> with task.associated_case however it seems Oracle doesn't allow you to use a column from the parent query in a JOIN.

So now I'm totally stumped. Is this possible? I'm sure with some PL/SQL magic it's easy but I don't think I'm able to use this.

Any tips would be greatly appreciated. Thanks

Upvotes: 1

Views: 373

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Two possible solutions, first starting from end to start, where we find leaves, which in these case are "original cases":

with 
  t as (select task_id, case_id from task),
  h as (select id, connect_by_root(id) root, connect_by_isleaf leaf
         from cases start with id in (select case_id from t)
         connect by prior parent_case = id)
select t.task_id, t.case_id, h.id top_case
  from t join h on t.case_id = h.root where leaf=1

SQLFiddle

Second, where at first I am buidling full hierarchy tree, next for each case_id in table tasks I am looking for proper case in hierarchy and show it's root.

with hierarchy as (
  select id, connect_by_root(id) root from cases
    start with parent_case is null 
    connect by parent_case = prior id) 
select task.task_id, task.case_id, h.root top_case
  from task join hierarchy h on task.case_id = h.id

SQLFiddle

Both queries produced same output for test data, in second I assumed that for "top cases" parent_case is null. First query should be faster as it builds hierarchy branches only for items we are interested in.

Upvotes: 0

Related Questions