Hi Mike, here's what Joe Celko proposes: Assuming lftValue is the left value of a certain record. Since all parents of the current record have left values that are smaller than lftValue and right values that ar larger than lftValue, you could use the following statement:
Select Count(*) From categories Where cat_lft <= #leftValueOfRecord# AND cat_rgt >= #leftValueOfRecord# So if you have the inheritance: A > B > C > D then D will return a count of 4 if you use <= and >=, and 3 if you use < and >... Hope it helps. <cfgreetings name="Gert Franz" location="Switzerland" email="[EMAIL PROTECTED]" function="Railo Core Developer"> Mike | NZSolutions Ltd schrieb: >Hi there, > >I have the following category table which utilises a nested set model... > >CREATE TABLE [dbo].[categories] ( > [cat_id] [int] IDENTITY (1, 1) NOT NULL , > [dModified] [datetime] NULL , > [dCreated] [datetime] NULL , > [cat_parent_id] [int] NULL , > [cat_lft] [int] NULL , > [cat_rgt] [int] NULL , > [cat_image] [varchar] (255) COLLATE Latin1_General_CI_AS NULL , > [cat_sort_order] [int] NULL >) ON [PRIMARY] >GO > >What I wish to add to the table is a level column, eg. the top most >parent nodes would be level 1, their children level 2 and so on... > >I am a bit stumped as to how to create a query (I am using SQL Server >2000) that will determine what level a particular record is in the >hierarchy? > >Any help would be really appreciated. > >mike > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230811 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
