Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Retrive parent child relation


Retrive parent child relation

Author
Message
vishnurajeshsat
vishnurajeshsat
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 24
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
PARENT CHILD CHILDQTY POSNO
1055874 1001544 2 1
1055874 5030036 1 2
1055874 4000069 2 3
1001544 1012327 1 1
1001544 1012328 1 2
1001544 1012329 1 3
1001544 1012330 6 4
1001544 1012331 2 5
1001544 3000191 4 6
1012327 2020003 5 1
1012328 2020003 9 1
1012329 2020003 4 1
1012330 2020001 6 1
1012331 2020003 8 1

PRODUCT2
PARENT CHILD CHILDQTY POSNO
1055872 1012345 1 1
1055872 5030036 5 2
1012345 1012325 1 1
1012325 2020005 6 1
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3001 Visits: 4995
You can use a CTE to do this

; WITH PRODUCT1 AS
(
SELECT *
FROM PRDST AS P
WHERE P.PARENT = 1055874
UNION ALL
SELECT PR.*
FROM PRDST AS PR
INNER JOIN PRODUCT1 AS PR1 ON PR.PARENT = PR1.CHILD
)

SELECT *
FROM PRODUCT1




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/
vishnurajeshsat
vishnurajeshsat
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 24
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.
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3001 Visits: 4995
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 *
FROM PRDST AS P
WHERE P.PARENT IN ( 1055874, 1055872 )
UNION ALL
SELECT PR.*
FROM PRDST AS PR
INNER JOIN PRODUCT1 AS PR1 ON PR.PARENT = PR1.CHILD
)

SELECT *
FROM PRODUCT1




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/
vishnurajeshsat
vishnurajeshsat
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 24
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.0000 1
1055872 5030036 0.0074 2
1055874 1001544 2.0000 1
1055874 5030036 0.0483 2
1055874 4000069 0.2800 3
1001544 1012327 1.0000 1
1001544 1012328 1.0000 2
1001544 1012329 1.0000 3
1001544 1012330 26.0000 4
1001544 1012331 2.0000 5
1001544 3000191 4.0000 6
1012331 2020003 0.5691 1
1012330 2020001 0.1530 1
1012329 2020003 0.6361 1
1012328 2020003 0.6361 1
1012327 2020003 0.4946 1
1012345 1012325 1.0000 1
1012325 2020005 0.9056 1
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3001 Visits: 4995
This might help you

; WITH PRODUCT1 AS
(
SELECT *, DENSE_RANK() OVER( ORDER BY P.PARENT ) AS RN, 1 AS Lvl
FROM PRDST AS P
WHERE P.PARENT IN ( 1055874, 1055872 )
UNION ALL
SELECT PR.*, PR1.RN, PR1.Lvl + 1
FROM PRDST AS PR
INNER JOIN PRODUCT1 AS PR1 ON PR.PARENT = PR1.CHILD
)

SELECT PARENT, CHILD, CHILDQTY, POSNO
FROM PRODUCT1
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/
vishnurajeshsat
vishnurajeshsat
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 24
Thank you it works:-)
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3001 Visits: 4995
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/
vishnurajeshsat
vishnurajeshsat
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 24
Got it thank u:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search