Oracle - junction tables and connect to prior

Question

I have problem that I think is only solvable with a Connect-to-prior statement.

My data model is, simplified, as follows:

create table TASK
 ( TASK_ID number
);
create table ITEM
 ( TASK_ID number,
   NEXT_TASK_ID number
);    
create table TASK_ITEM
 ( TASK_ID number,
   ITEM_ID number
);

a set of items are combined into a task. A set for tasks are combined into one or more items (this is where the TASK_ITEM junction-table comes in). The process starts with a set of items, and ends with a single item (ITEM.NEXT_TASK_ID is null for the last item, and no task_item's exist for the first items).

My problem:

Given one task_id I want to extract all preceding tasks.

This is a problem bordering on those described in Philip Greenspun's excellent introduction to trees in Oracle, or in Wikibook's description of Hierarchical Queries, but can't seem to find any good solutions tackling these problems when they include more ID's and a Junction table.

My own SQL-foo falls sadly short, and I've googled my heart out without finding a solution to my particular problem.

In the voice of Carrie Fisher: Help me Stack Overflow, you're my only hope.

Could you include a few rows of data? [it looks like some information might be redundant in your model] — Vincent Malgrat, Feb 21 '13 at 12:06
Shouldn't "a set of items are combined into a task" rather read "a set of task items are combined into a task" ? — Frank Schmitt, Feb 21 '13 at 12:13
@phareim: Your problem is `"Given one task_id I want to extract all preceding tasks."` ,then why we need table `TASK_ITEM AND TASK ` It can be easily found by Only one table ITEM ?? — Gaurav Soni, Feb 21 '13 at 12:20

score 0 · Accepted Answer · answered Feb 21 '13 at 13:11

Seek and you shall find.

With the help of a colleague we sorted this out.

The trick I hadn't managed to see for my self is to first join the required information together, then do the connect to-statement.

I think the code below solves the problem as it's described.

select parent_task.* 
    from (
    select  parent_task_id, item_id, task_id, level
        from (
            select 
                task.task_id parent_task_id, 
                task_item.item_id, 
                item.task_id task_id
            from 
                task, task_item, item
            where 
                task_item.task_id = task.task_id
            and 
                item.item_id = task_item.item_id) properly_structured_parent_table    
        start with task_id = :TASK_ID
        connect by prior parent_task_id = task_id 
    ) task_hierarchy, task parent_task
where parent_task.task_id = task_hierarchy.task_id;

The inner mot SQL builds a proper parent_table. The next build a hierarchy using connect by prior. The outermost SQL joins in the rest of the data as required in the application.

Thank you for your time and feedback folks.

Oracle - junction tables and connect to prior

1 Answers1