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: 4111 More actions · Answer 1

Shaun McGuile

SSCarpal Tunnel

Points: 4111

January 9, 2008 at 7:36 am

#766193

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: 1000539 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)
Intro to Tally Tables and Functions

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

Shaun McGuile

SSCarpal Tunnel

Points: 4111

January 9, 2008 at 7:49 am

#766204

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: 1000539 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)
Intro to Tally Tables and Functions

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

Shaun McGuile

SSCarpal Tunnel

Points: 4111

January 9, 2008 at 8:14 am

#766226

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: 1000539 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)
Intro to Tally Tables and Functions

Shaun McGuile SSCarpal Tunnel Points: 4111 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: 1000539 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)
Intro to Tally Tables and Functions

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 =

CASE WHEN 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: 4111 More actions · Answer 10

Shaun McGuile

SSCarpal Tunnel

Points: 4111

January 10, 2008 at 2:00 am

#766510

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: 8633 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