Re: A hierarchical query

From: oracle_doc <nilendu_at_nilendu.com>
Date: 19 Feb 2006 10:47:51 -0800
Message-ID: <1140374871.092623.289340@g47g2000cwa.googlegroups.com>

It's so easy to denounce people these days! Anyways, Thanks again Mladen for your candid expert's view.

Let's accept that this is a bad data model and I put the successor_id in the TASKS table.

CREATE TABLE TASKS
(

  PREDECESSOR_ID         NUMBER                             NOT NULL,
  NAME       VARCHAR2(200 BYTE)                 NOT NULL,

PARENT_ID NUMBER,
SUCCESSOR_ID NUMBER
);

PREDECESSOR_ID      NAME    PARENT_ID      SUCCESSOR_ID
1                                  A
2                                  a             1
3
3                                  b             1
4
4                                  c             1

In other words, this table has two parent-child relationships. PARENT_ID - PREDECESSOR_ID and PREDECESSOR_ID - SUCCESSOR_ID.

Let's also assume that there could be only one PREDECESSOR for one SUCCESSOR; and there could be only one PARENT for one PREDECESSOR.

So, the question is - is this possible to use just a SELECT and get data back in form so that -
(1) predecessors before parents
(2) predecessors before successors

i.e., for this table - the output data should be --

[a b c A]

And, again, if this is "bad design", "insulting Oracle", "trying to use buffer hit ratio and therefore should go to hell" - I'm fine if you don't write back. DBA community these days have become ultra-critical of everything that doesn't follow the pattern. (sigh!) Received on Sun Feb 19 2006 - 12:47:51 CST

Re: A hierarchical query - how do I do