Just Implemented Nested Lists - SQL Server Forums

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

SQL Server Development (2000)

Just Implemented Nested Lists

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2001-12-12 : 03:30:32

I finally got around to giving Joe Celko's nested lists a shot. Everything was going just fine until I tried combining two queries. I want to be able to return all of the items from a certain location down, *and* get the appropriate indentation value for it. The main portion works fine, but the indent seems to always be either all 1s, 7s, or 16s. Any help would be most appreciated.

 
SELECT 
	Indent = 
	(
	SELECT COUNT(Cat4.CategoryId)
	FROM tblCategories AS Cat3, tblCategories AS Cat4
	WHERE Cat4.LBound BETWEEN Cat3.LBound AND Cat3.UBound

	),
	Cat2.CategoryId,
	Cat2.CategoryName,
	Cat2.CategoryDescription,
	Cat2.LBound,
	Cat2.UBound
FROM 
	tblCategories AS Cat1, 
	tblCategories AS Cat2
WHERE 
	Cat2.LBound BETWEEN Cat1.LBound AND Cat1.UBound
	AND Cat1.CategoryId = @CategoryId
ORDER BY
	Cat2.LBound

Also, does anyone know how you could SELECT just one level of items? Like, just the supervisors, not their underlings, not the higher ups, just the supervisors?

Nazim
A custom title

1408 Posts

Posted - 2001-12-12 : 05:12:19

quote:

 
	Indent = 
	(
	SELECT COUNT(Cat4.CategoryId)
	FROM tblCategories AS Cat3, tblCategories AS Cat4
	WHERE Cat4.LBound BETWEEN Cat3.LBound AND Cat3.UBound

	),

A quick look on ur query dont u think u missed up joining cat3 and cat4, something like this
Indent =
(
SELECT COUNT(Cat4.CategoryId)
FROM tblCategories AS Cat3, tblCategories AS Cat4
WHERE Cat3.CategoryId=Cat4.Categoryid and Cat4.LBound BETWEEN Cat3.LBound AND Cat3.UBound
)

got to hurry , will catch up tommorow on this thread:)

-------------------------
"Success is when Preparedness meets Opportunity"

Edited by - Nazim on 12/12/2001 05:12:50

hande
Starting Member

8 Posts

Posted - 2001-12-12 : 06:47:03

well, I thnk i hava problem solved, although It took time because of one novice asked me too much silly questions of SELECTs etc. No time for pepsi.
You know, he try to look too smart...

Blastrix
Posting Yak Master

208 Posts

Posted - 2001-12-12 : 10:25:53

quote:

A quick look on ur query dont u think u missed up joining cat3 and cat4, something like this
Indent =
(
SELECT COUNT(Cat4.CategoryId)
FROM tblCategories AS Cat3, tblCategories AS Cat4
WHERE Cat3.CategoryId=Cat4.Categoryid and Cat4.LBound BETWEEN Cat3.LBound AND Cat3.UBound
)

Well that's what I had tried before, and that version returned either all 1s, or all 7s, I can't remember which. I just know that it didn't do what I wanted it to.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources