Retrive parent child relation

  • Hi all below is my table and I want query for parent child relation.

    CREATE TABLE [dbo].[PRDST](

    [PARENT] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [CHILD] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [CHILDATY] [decimal](13, 4) NULL,

    [POSNO] [smallint] NOT NULL CONSTRAINT [DF__PRDST__MPTPST__3D2915A8] DEFAULT (0),

    CONSTRAINT [PKPARCHR] PRIMARY KEY CLUSTERED

    (

    [PARENT] ASC,

    [POSNO] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1055874' , '1001544' , '2' , '1')

    INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1055874' , '5030036' , '1' , '2')

    INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1055874' , '4000069' , '2' , '3')

    INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1001544' , '1012327' , '1' , '1')

    INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1001544' , '1012328' , '1' , '2')

    INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1001544' , '1012329' , '1' , '3')

    INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1001544' , '1012330' , '6' , '4')

    INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1001544' , '1012331' , '2' , '5')

    INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1001544' , '3000191' , '4' , '6')

    INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1012327' , '2020003' , '5' , '1')

    INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1012328' , '2020003' , '9' , '1')

    INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1012329' , '2020003' , '4' , '1')

    INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1012330' , '2020001' , '6' , '1')

    INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1012331' , '2020003' , '8' , '1')

    INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1055872' , '1012345' , '1' , '1')

    INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1055872' , '5030036' , '5' , '2')

    INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1012345' , '1012325' , '1' , '1')

    INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1012325' , '2020005' , '6' , '1')

    In the above table I had updated 2 products(1055874 , 1055872 ).

    Each parent has child(one or more than one and ) then child become parent(not all the child becomes parent) like that it go upto some level then it stops.

    I know only the 1st parent of all products(105 series) So once if I query for 1055874 then it has to retrive upto the last

    parent(1012331) as like below and the posno is in asc. Can some one help me.

    PRODUCT1

    PARENTCHILDCHILDQTYPOSNO

    10558741001544 2 1

    10558745030036 1 2

    10558744000069 2 3

    10015441012327 1 1

    10015441012328 1 2

    10015441012329 1 3

    10015441012330 6 4

    10015441012331 2 5

    10015443000191 4 6

    10123272020003 5 1

    10123282020003 9 1

    10123292020003 4 1

    10123302020001 6 1

    10123312020003 8 1

    PRODUCT2

    PARENTCHILDCHILDQTYPOSNO

    10558721012345 1 1

    10558725030036 5 2

    10123451012325 1 1

    10123252020005 6 1

  • You can use a CTE to do this

    ; WITH PRODUCT1 AS

    (

    SELECT*

    FROMPRDST AS P

    WHEREP.PARENT = 1055874

    UNION ALL

    SELECTPR.*

    FROMPRDST AS PR

    INNER JOIN PRODUCT1 AS PR1 ON PR.PARENT = PR1.CHILD

    )

    SELECT*

    FROMPRODUCT1


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Thank you kingston it works but if i want to get more than 1 product details (i.e. 1055874 , 1055872 , etc. ) then how to change the query.

  • vishnurajeshsat (5/21/2013)


    Thank you kingston it works but if i want to get more than 1 product details (i.e. 1055874 , 1055872 , etc. ) then how to change the query.

    Something like this

    ; WITH PRODUCT1 AS

    (

    SELECT*

    FROMPRDST AS P

    WHEREP.PARENT IN ( 1055874, 1055872 )

    UNION ALL

    SELECTPR.*

    FROMPRDST AS PR

    INNER JOIN PRODUCT1 AS PR1 ON PR.PARENT = PR1.CHILD

    )

    SELECT*

    FROMPRODUCT1


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • I tried but sequence is not correct 1012345 is child of 1055872 then 1012345 has to become a parent but result is not like that. It has to return like a chain link

    Result

    parent child childqty posno

    1055872 1012345 1.00001

    1055872 5030036 0.00742

    1055874 1001544 2.00001

    1055874 5030036 0.04832

    1055874 4000069 0.28003

    1001544 1012327 1.00001

    1001544 1012328 1.00002

    1001544 1012329 1.00003

    1001544 1012330 26.00004

    1001544 1012331 2.00005

    1001544 3000191 4.00006

    1012331 2020003 0.56911

    1012330 2020001 0.15301

    1012329 2020003 0.63611

    1012328 2020003 0.63611

    1012327 2020003 0.49461

    1012345 1012325 1.00001

    1012325 2020005 0.90561

  • This might help you

    ; WITH PRODUCT1 AS

    (

    SELECT*, DENSE_RANK() OVER( ORDER BY P.PARENT ) AS RN, 1 AS Lvl

    FROMPRDST AS P

    WHEREP.PARENT IN ( 1055874, 1055872 )

    UNION ALL

    SELECTPR.*, PR1.RN, PR1.Lvl + 1

    FROMPRDST AS PR

    INNER JOIN PRODUCT1 AS PR1 ON PR.PARENT = PR1.CHILD

    )

    SELECTPARENT, CHILD, CHILDQTY, POSNO

    FROMPRODUCT1

    ORDER BY RN, Lvl, PARENT


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Thank you it works:-)

  • vishnurajeshsat (5/23/2013)


    Thank you it works:-)

    Glad it works for you and I hope you understand what it does 🙂

    In case you don't, make sure you understand it by looking up CTE and DENSE_RANK() on Google or Books Online


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Got it thank u:-)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply