PS: Note that your query to obtain the level is *only* correct if pin is unique (i.e. define a unique constraint/index on the column).
Tore. -----Original Message----- From: Bostrup, Tore [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 15, 2002 9:52 AM To: ActiveServerPages Subject: RE: SQL Nested Set Model Question 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.unsub%% --- You are currently subscribed to activeserverpages as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED]
