Parent Child Query

I have a table and some same data.
entity_id | parent_entity_id | entity_name
-- -- --
4 0 Address Control
5 4 Address Line 1
6 4 Address Line 2
7 4 City
8 4 State
9 4 Zip Code
10 8 Maine
11 8 Massachusetts
12 8 New Hampshire
13 8 Rhode Island
14 8 Vermont
15 8 Connecticut
I'd like to pass in the entity_id, in this case 4 and get back all of the
children and grandchildren and I possibly down N, but would be happy with
grandchildren at this point as I dont know if deeper is needed.
If someone could point me to an article maybe or old post. I have looked
around some but see a lot of Oracle stuff.
Thanks"Brian" <brian@.nospam.com> wrote in message
news:uzKYuJqnFHA.1416@.TK2MSFTNGP09.phx.gbl...
>I have a table and some same data.
> entity_id | parent_entity_id | entity_name
> -- -- --
> 4 0 Address Control
> 5 4 Address Line 1
> 6 4 Address Line 2
> 7 4 City
> 8 4 State
> 9 4 Zip Code
> 10 8 Maine
> 11 8 Massachusetts
> 12 8 New Hampshire
> 13 8 Rhode Island
> 14 8 Vermont
> 15 8 Connecticut
> I'd like to pass in the entity_id, in this case 4 and get back all of the
> children and grandchildren and I possibly down N, but would be happy with
> grandchildren at this point as I dont know if deeper is needed.
> If someone could point me to an article maybe or old post. I have looked
> around some but see a lot of Oracle stuff.
> Thanks
>
Get Celko's books on Trees and Hierarchies. Other items of interest, you
can google for nested set theory.
Rick Sawtell|||The following example includes a function that can be used to find
descendants and ascendants of an item in a hierarchy.
http://milambda.blogspot.com/2005/0...or-monkeys.html
A vital element is missing from your model - prevention of circular
references.
ML

Parse IP address

I am fairly new to SQL and am trying to write a function to parse the ip address into 4 sections. I have been searching through the forums to see if anyone has a posted example of parsing an ip address but could not find one.

I am wondering what would be the best method of doing this, or if anyone has an example.

Thank youlook at SUBSTRING and CHARINDEX in books online and think about how you could nest them.|||I found a better answer, builtin function PARSENAME

takes the 4 sections between the . and seperates them. only 1 line of code for each section:

,PARSENAME(ip, 4) AS 'Sec1'
,PARSENAME(ip, 3) AS 'Sec2'
,PARSENAME(ip, 2) AS 'Sec3'
,PARSENAME(ip, 1) AS 'Sec4'

Thanks|||Holy non-standard usage Batman! :shocked:

Just goes to show what a motivated user will do (and drive the developers crazy in the process).

Kudos to you for the unorthodox approach. Hope it works.

Regards,

hmscott|||I found a better answer, builtin function PARSENAME

takes the 4 sections between the . and seperates them. only 1 line of code for each section:


,PARSENAME(ip, 4) AS 'Sec1'
,PARSENAME(ip, 3) AS 'Sec2'
,PARSENAME(ip, 2) AS 'Sec3'
,PARSENAME(ip, 1) AS 'Sec4'

Lol - that is brilliant.
There have been a few discussions on how to store IP addresses (char, 4 tinyints, integer etc). This is a new way on me to split the human-friendly form though!|||yup it works great! Def the easiest way to do it :)|||if you have a string with more parts, try this one:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033