Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    Brazil
    Posts
    4

    Exclamation Unanswered: Search Hierachical Data

    Hi,

    i have a table tath looks like this:

    SQL>SELECT * FROM TB_DATA;


    ID PARENT_ID VALUE
    --------- --------- ------------------------------
    18584 0010036143
    4924 00204346308046
    18102 010
    3539 3481 01020E03002272
    3477 3481 01020E03002324
    9375 3477 01020E03002477
    10654 9375 01020E03002510
    13664 10654 01020E03002608
    3481 01020E0302272
    7419 5163 03333162
    15990 7493 03333725
    5163 073035440
    6320 7493 073035580
    7493 5163 073036340
    11801 5163 073040210
    4661 08183333

    16 rows selected.

    ID - table id
    PARENT_ID - if it is not null means that the record has a parent record
    VALUE - the value

    and i'm using the CONNECT BY CLAUSE to retrieve a hierarchical tree and it's working fine:

    SQL>SELECT LEVEL, ID,VALUE
    FROM TB_DATA
    START WITH PARENT_ID IS NULL
    CONNECT BY PRIOR ID = PARENT_ID;


    LEVEL ID VALUE
    --------- --------- ------------------------------
    1 18584 0010036143
    1 4924 00204346308046
    1 18102 010
    1 3481 01020E0302272
    2 3539 01020E03002272
    2 3477 01020E03002324
    3 9375 01020E03002477
    4 10654 01020E03002510
    5 13664 01020E03002608
    1 5163 073035440
    2 7419 03333162
    2 7493 073036340
    3 15990 03333725
    3 6320 073035580
    2 11801 073040210
    1 4661 08183333

    16 rows selected.


    Now i would like to retrieve all the parent records searching by the VALUE column. I can't get there. I've tried a lot of ways to do it but can't get the result.


    EX: find all the parent records to '01020E03002608' ...

    I need some help here ... THANKS!!!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2004
    Location
    Brazil
    Posts
    4
    Thanks but it does not aply.

    See, I need a query that results in all nested parent records to a given value.

    ex:
    [pre]
    SQL> SELECT * FROM SAMPLE_TABLE;

    id parent_id value
    ---------------------------------
    12 | <null> | 'HOUSE'
    47 | 12 | 'BED'
    67 | 47 | 'LAMP'
    78 | <null> | 'FARM'
    45 | 67 | 'LIGHT'
    14 | 67 | 'HEART'
    83 | 45 | 'CAR'
    34 | <null> | 'MOTO'

    [/pre]

    I want a query that i'll give for example 'CAR' and will retrieve 'LIGHT', 'LAMP' (parent of 'CAR'), 'BED' (parent of 'LAMP') and 'HOUSE' (parent of 'BED').

    The Result of the query should be:
    [pre]
    SQL> .... AND VALUE = 'CAR';

    level id parent_id value
    --------------------------------------
    1 | 12 | <null> | 'HOUSE'
    2 | 47 | 12 | 'BED'
    3 | 67 | 47 | 'LAMP'
    4 | 45 | 67 | 'LIGHT'
    5 | 83 | 45 | 'CAR'

    [/pre]

    Thanks.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL> create table btree
    2 ( id number,
    3 pid number,
    4 value varchar2(31)
    5 );
    Table created.
    SQL> insert into btree values(12,null,'HOUSE');
    1 row created.
    SQL> insert into btree values(47 , 12 , 'BED');
    1 row created.
    SQL> insert into btree values(67 , 47 , 'LAMP');
    1 row created.
    SQL> insert into btree values(78 , null , 'FARM');
    1 row created.
    SQL> insert into btree values(45 , 67 , 'LIGHT');
    1 row created.
    SQL> insert into btree values(14 , 67 , 'HEART');
    1 row created.
    SQL> insert into btree values(83 , 45 , 'CAR');
    1 row created.
    SQL> insert into btree values(34 , null , 'MOTO');
    1 row created.
    SQL> commit;
    Commit complete.
    SQL> select level, id, pid, value
    2 from btree
    3 connect by prior id = pid
    4 start with pid IS null;

    LEVEL ID PID VALUE
    ---------- ---------- ---------- -------------------------------
    1 12 HOUSE
    2 47 12 BED
    3 67 47 LAMP
    4 45 67 LIGHT
    5 83 45 CAR
    4 14 67 HEART
    1 78 FARM
    1 34 MOTO

    8 rows selected.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2004
    Location
    Brazil
    Posts
    4
    Could anybody help me with the query?

    Thanks.

  6. #6
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Haven't tried it, but if memory serves me correctly, this might work:

    SELECT LEVEL, ID,VALUE
    FROM TB_DATA
    START WITH VALUE = '01020E03002608'
    CONNECT BY PRIOR ID = PARENT_ID;

    Might give it a shot.

    JoeB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •