how to find the parent

Question

Post reply

how to find the parent

Viewing 13 posts - 16 through 27 (of 27 total)

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

Shaun McGuile SSCarpal Tunnel Points: 4141 More actions · Answer 1

Jeff do you have a link to Celko's "nested model"?

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy:

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Shaun McGuile (1/9/2008)
Jeff do you have a link to Celko's "nested model"?
--Shaun

You bet...

http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

If memory serves, the code that creates the nested model has a minor error in it... it leaves off the last node... don't remember precisely what I did to fix it but I do remember it was a minor tweak.

In actuality, much of the hierarchical data I have to work with requires that I also keep the adjacency model... so I build "sister" tables of ID's to keep the "nested model" in kinda like what Matt said about his hierarchies.

The only thing you really have to be very careful of in any of these hierarchies is not to make "hierarchical loops". The only way to absolutely guarantee that doesn't happen is to keep the hierarchy 100% positional... then, even when one person reports to many bosses, it just shows up as more unique positions in the hierarchy that have no "hierarchical loops".

--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)

Shaun McGuile SSCarpal Tunnel Points: 4141 More actions · Answer 3

Ok I found it

http://www.dbmsmag.com/9603d06.html

Whoa thats like 12 years old almost. 😀

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy:

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

Shaun McGuile (1/9/2008)
Ok I found it
http://www.dbmsmag.com/9603d06.html
Whoa thats like 12 years old almost. 😀
--Shaun

Heh... yeah... the good stuff never changes 😀

--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)

Shaun McGuile SSCarpal Tunnel Points: 4141 More actions · Answer 5

I see how it deals with finite known sets of data, but how does it cope with new records, positional changes and deletions?

Or is this too big a topic to discuss in this thread?

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy:

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

He has code that inserts and increases the left and right indicies... most of my code is batch code so I just recalc the whole tree from the adjacency model.

--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)

Shaun McGuile SSCarpal Tunnel Points: 4141 More actions · Answer 7

Don't worry I found the solution on the great information super landfill (the real reason for global warming) the interweb.

One uses left/right values of a defined range for each node and sub nodes exist as subordinates within the parent node's range

http://searchoracle.techtarget.com/tip/1,289483,sid41_gci1072361,00.html

One needs to register, but its free.

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy:

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

Perfect... thanks for the feedback, Shaun.

To keep conflicts with the Left and Right keywords to a minimum, I use LeftBower and RightBower in my code. A "bower" is one of the two anchors on the front of a ship... seemed like something having to do with "anchors" was appropriate here... 😛

--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)

BrentMc Old Hand Points: 383 More actions · Answer 9

Hi all,

I do have two columns... but what I've given you is just the child column and I wanted to find the parent from that column and update the parent column. This is used in a medical coding system (not legal as someone thought).

I worked it out this way...

update

set parent_code =

CASEWHEN LEN(code) = 2 THEN NULL

WHEN LEN(code) = 4 THEN SUBSTRING(code, 1, 2)

WHEN LEN(code) = 5 THEN SUBSTRING(code, 1, 4) ELSE NULL END

when the length is only 2 then it is a top level parent so it is NULL

cheers.

Shaun McGuile SSCarpal Tunnel Points: 4141 More actions · Answer 10

Everyone not in the know should read this;

http://www.sqlteam.com/article/more-trees-hierarchies-in-sql

--Shaun

'spreading something that could be knowledge'

Hiding under a desk from SSIS Implemenation Work :crazy:

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 11

Shaun McGuile (1/9/2008)
The question is wrong on the basis of design and the modeling of the parent child relationship using one column is totally flawed.

Brent McCracken (1/10/2008)
I do have two columns... but what I've given you is just the child column and I wanted to find the parent from that column and update the parent column. This is used in a medical coding system (not legal as someone thought).

While Shaun is correct that a database in 'production mode' would probably be more efficient using 2 columns for parent and child, when loading data from other systems it's often necessary to construct a parent-child relationship from one column of data, which is what Brent seems to be doing.

Sometimes the conversion and loading processes will be very inefficient, but if they are only done once and the derived tables are properly designed, it ususally doesn't matter.

Derek

bryan.reynolds SSC Rookie Points: 41 More actions · Answer 12

Lurking to lurn, but I thought that I recognized ICD9 codes.

find, use, share, expand

Shaun McGuile SSCarpal Tunnel Points: 4141 More actions · Answer 13

DNA test them...oh sorry this is not the CSI forum?

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy: