1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help on using hierarchy query

Discussion in 'SQL PL/SQL' started by Shanmugapriya, Mar 30, 2017.

  1. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    55
    Likes Received:
    0
    Trophy Points:
    130
    Location:
    Bangalore
    Need help on hierarchy query.

    I have two tables NSL and NSL_COMMENT
    They both are joined using nsl pk column and nsl_comment fk column

    The requirement is that we need to get the first, second, third and so on up to last record using the below mentioned logic

    Code (SQL):

    nsl.p_k                 nsl.nk_s_nsl_comm      nc.p_k            nc.nk_s_nsl_comm       nc.fk_s_nsl_nsl_comm

    100004805615005        100004805615006          100004805615006        100004805615007         100004805615005
    100004805615005         100004805615006       100004805615007       100004805615008         100004805615005
    100004805615005         100004805615006       100004805615008       100004805615009         100004805615005
    100004805615005         100004805615006       100004805615009       100004805615005         100004805615005
     
    first record in the child table : nsl.nk_s_nsl_comm from the parent table gives the value to be used in nc.p_k to get first record which is 100004805615006

    Second record in the child table: using first record nc.p_k 100004805615006, we have nc.nk_s_nsl_comm which is 100004805615007, now this becomes nc.p_k for second record

    Third record in the child table : using second record nc.p_k 100004805615007, we have nc.nk_s_nsl_comm which is 100004805615008, now this becomes nc.p_k for third record

    Fourth record in the child table: using third record nc.p_k 100004805615008, we have nc.nk_s_nsl_comm which is 100004805615009, now this becomes nc.p_k for fourth record

    Last record in the child table: nc.fk_s_nsl_nsl_comm = nc.nk_s_nsl_comm gives the last record.


    Kindly help
     

    Attached Files:

    Last edited by a moderator: Mar 30, 2017
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,639
    Likes Received:
    368
    Trophy Points:
    1,430
    Location:
    Aurora, CO
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    768
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation

    For education
    : Hierarchical Query

    Simple example:
    Code (SQL):
    WITH
    nsl(p_k,nk_s_nsl_comm) AS
    (
      SELECT '100004805615005','100004805615006'  FROM dual
    ),
    nc (p_k,nk_s_nsl_comm,fk_s_nsl_nsl_comm)
    AS
    (
      SELECT '100004805615006','100004805615007','100004805615005'  FROM dual UNION ALL
      SELECT '100004805615007','100004805615008','100004805615005'  FROM dual UNION ALL
      SELECT '100004805615008','100004805615009','100004805615005'  FROM dual UNION ALL
      SELECT '100004805615009','100004805615005','100004805615005'  FROM dual
    ),
    h_nc AS
    (
    SELECT nc.* FROM nc
    CONNECT BY p_k = PRIOR nk_s_nsl_comm
    )
    SELECT t.*,t1.* FROM nsl t,nc t1
    WHERE t.p_k = t1.fk_s_nsl_nsl_comm;
     
     
    Last edited by a moderator: Mar 31, 2017