How to find the childrens path of a given parent

Question

Post reply

How to find the childrens path of a given parent

vamshi krishna-303410

Right there with Babe

Points: 790
More actions
July 29, 2007 at 2:29 am

#117967

consider the following scanario of categories table
CatID   ParentID    CategoryName
---------------------------------
1    0              Laptops
2    1              Brand New
3    1              Refurbished
4    1              Secondhand
5    0              Desktops
6    5              Brand New
7    5              Refurbished
8    5              Secondhand
9    5              Custom Built
10   0              Laptop Spare Parts
11   10             Hard Drives
12   11            1.8
13   11             2.5
14   10             Memory RAM
15   14             PC100
16   14             PC133
17    14            DDR
18    14            DDR2
19    14            Other Specific
20    10            AC Adapters
21    20           Toshiba
22    20           Samsung
23    20           ACER
childrens path for a given category 10 is 11 to 23, for 14 15 to 19 for , how can i find the childrens of the category based on their categoryID??
~vamshi krishna~

Viewing 15 posts - 1 through 15 (of 23 total)

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Same question again?

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=381713

I think you will be better served if you paid attention to the suggestion you are given, instead of just asking same question again.

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

CREATE FUNCTION DBO.FNGETHIERARCHY

(@CategoryID INT)

RETURNS @Peso TABLE (@CategoryID INT, @CategoryName VARCHAR(100), @Depth INT)

AS

BEGIN

DECLARE @Generation INT

DECLARE @Depth TABLE(

GENERATION INT,

CATID INT,

NAME INT

)

SET @Generation = 0

INSERT @Depth

SELECT 0,

CatID,

CategoryName

FROM [SAMPLE]

WHERE CatID = @CategoryID

WHILE @@ROWCOUNT > 0

BEGIN

SELECT @CategoryID = CATID,

@Generation = @Generation + 1

FROM @Depth

WHERE GENERATION = @Generation

INSERT @Depth

SELECT @Generation,

S.PARENTID,

S.CategoryName

FROM [SAMPLE] AS S

WHERE S.CATID = @CategoryID

AND NOT EXISTS (SELECT *

FROM @Depth AS X

WHERE X.CATID = S.PARENTID)

END

SELECT @CategoryID = MIN(CATID)

FROM @Depth

IF @CategoryID = 0

INSERT @peso

SELECT CatID,

Name,

Generation

FROM @Depth

ORDER BY Generation

RETURN

END

N 56°04'39.16"
E 12°55'05.25"

vamshi krishna-303410 Right there with Babe Points: 790 More actions · Answer 3

peter my previous question was about finding the depth, now i am with different question.

~vamshi krishna~

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 4

Did you even look at Peter's code, Vamshi? Looks to me like it returns more than just depth... he even changed the title of the proc so you wouldn't get confused. Not much more anyone can do unless you need to be spoon fed with a sling shot

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vamshi krishna-303410 Right there with Babe Points: 790 More actions · Answer 5

yeap, i saw peter's previous code for function and it is really helpful for me for finding depth, i dont find any other values it returns otherthen level/depth.

~vamshi krishna~

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 6

He even changed the name!!! Did you look at THIS piece of code?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vamshi krishna-303410 Right there with Babe Points: 790 More actions · Answer 7

ya i saw this code...it returns more then just a depth

~vamshi krishna~

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 8

OK... Now I see and I've got a bit of egg on my face... there's actually a couple of errors in Peter's code

Sorry Vamshi.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vamshi krishna-303410 Right there with Babe Points: 790 More actions · Answer 9

Peter how to union all existing tuples in the table as in your previous solution you inserted each tuple by union all, how can we union all the existing tuples in the table? can we make your function to work with non union natural tuples? reply soon peter.

~vamshi krishna~

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 10

Jeff, don't be sad.

Vamshi hasn't told us how he wants the expected result to be, not formatted.

My code above produces (with small alterations to fit Vamshis envionment) all children with depth below, and with the inputted categoryid.

Still waiting for expected result.

N 56°04'39.16"
E 12°55'05.25"

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Hey Peter, can you tell us what your signature is supposed to represent?

E 12°55'05.76"

N 56°04'39.42"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 12

Okay..

Peter Larsson

Or the signature below my responses? It's the coordinates for my new house!

If someone gets mad at me and want to launch a missile at me...

N 56°04'39.16"
E 12°55'05.25"

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Launch a missile? Good lord no...here's some pizza launched to say thanks for fixing holes in my dodgy code! Good luck with your new gaff.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

vamshi krishna-303410 Right there with Babe Points: 790 More actions · Answer 14

Peter, is there a possible way to make this function work with a table which tuples are not unioned? or could u plz tell me how can i union all the existing tuples, as you posted in the previous answer it shows inserting each tuple with union all preceding each new insert, how can i union all the existing tuples in my products category table? reply soon.

~vamshi krishna~