Is this what you are looking for:
Select v.* from customerstackview as c, (
Select p2.pin, p2.fname, p2.lname,
p2.email, p2.lft, p2.rgt,
count(p1.pin) as Level
from customerstackview p1,customerstackview p2
where p2.lft between p1.lft and p1.rgt
group by p2.pin, p2.fname, p2.lname,
p2.email, p2.lft, p2.rgt ) as v
Where c.pin = '11101'
And v.lft between c.lft and c.rgt
And v.pin <> c.pin -- if you don't want to show the "root" (pin='11101')
Order By Level
It may be possible to optimize this statement...
HTH,
Tore.
-----Original Message-----
From: Steve Abaffy [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 14, 2002 11:03 AM
To: ActiveServerPages
Subject: OT: SQL Nested Set Model Question
Hello,
(If this is a repeat of the question my apologies, it did not appear to have
gone through the first time)
I have been reading Joe Celko's book on representing trees in SQL, and have
converted my Adjacency List Model to a Nested Set Model he suggest that to
get the level of a node in the tree to run the following query:
Select p2.pin,count(p1.pin) as Level
from customerstackview p1,customerstackview p2
where p2.lft between p1.lft and p1.rgt
and p2.pin='11101'
group by p2.pin
Which accurately gets the level for pin number 11101
Then to get all the entire subtree for a any node
select
p2.pin,p2.fname,p2.lname,p2.email
from
CustomerStackView as p1,CustomerStackView as p2
where
p2.lft between p1.lft and p1.rgt and p1.pin <> p2.pin
and p1.pin='11101'
order by p2.lft
Select p2.cdipin,count(p1.cdipin) as Level
from customerstackview p1,customerstackview p2
where p2.lft between p1.lft and p1.rgt
and p2.cdipin='11101'
group by p2.cdipin
Which also works like a champ. Now the problem is I would like to get the
levels of each record returned by the second query and I can't figure out
how to put the two queries together.
Any help would be greatly appreciated.
---
You are currently subscribed to activeserverpages as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
%%email.unsub%%
---
You are currently subscribed to activeserverpages as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
