celko nested model: how to compute the lft rgt col

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

celko nested model: how to compute the lft rgt col

Author

Topic

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2006-07-03 : 18:03:17

Hi,

With reference to http://www.intelligententerprise.com...questid=235427
I want the "sql stmt" which wud give the lft and rgt col values..

i am reading his book but cant understand where he explains
wat lft and rgt cols are..

"The root is always (lft,rgt) (1, 2*(Select count(*)from table) and leaft nodes are (lft+1=rgt)"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-07-03 : 19:35:27

first of all fix the link because it doesn't work,
because i have no idea what you need.
So i'm guessing it's explained in the link.

Go with the flow & have fun! Else fight the flow

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-03 : 20:46:49

You should ask Celko to explain it. It's his book.

CODO ERGO SUM

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2006-07-03 : 22:41:34

i had posted the correct link

http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=235427

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2006-07-03 : 22:43:08

Michael Valentine Jones
You should ask Celko to explain it. It's his book.

>> if tats the case y do we have forums like these??

newayz i have sent him a mail 2...

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-04 : 06:01:46

It represents this structure:

Albert
  Bert
  Chuck
    Donna
    Eddie
    Fred

Look at it like this:

1Albert
  2Bert3
  4Chuck
    5Donna6
    7Eddie8
    9Fred10
  11
12

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2006-07-04 : 13:22:51

thanks for the reply Ryan..
i understood how to look @ the hierarchy but how to derive the values of lft and rgt cols is my question...i mean thru sql...

y is Chuck not 2Chuck3??

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-04 : 13:50:02

quote:
how to derive the values of lft and rgt cols is my question...i mean thru sql...

You mean how to derive the values of lft and rgt cols from the emp and boss cols? If so, that's at the end of that same article. If not, what?

quote:
y is Chuck not 2Chuck3??

I don't understand. 2 and 3 have already been used (for Bert) and you can only use a number once.

If your question is "Why is Chuck not 4Chuck5?", then it's because Chuck is not a leaf node. He has "children".

If you think of it as xml...

<emp name="Albert">
  <emp name="Bert"/>
  <emp name="Chuck">
    <emp name="Donna"/>
    <emp name="Eddie"/>
    <emp name="Fred"/>
  </emp>
</emp>

...then put a number (working downwards, consecutively) at the start (i.e. <emp) and end (i.e. /) of every node, then you get your numbers.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2006-07-04 : 15:12:58

thanks a lot..

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-04 : 23:13:38

quote:
Originally posted by 2lazydba
...if tats the case y do we have forums like these??

newayz i have sent him a mail 2...

Can I get the English translation?

CODO ERGO SUM

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-05 : 05:37:59

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by 2lazydba
...if tats the case y do we have forums like these??

newayz i have sent him a mail 2...

Can I get the English translation?

CODO ERGO SUM

My interpretation was:

...if that's the case why do we have forums like these?

Anyway, I have sent him a mail too...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-07-05 : 07:51:24

Makes you wonder what sort of reply he'll get from Joe Celko if the email is written in the same vein, doesn't it?