SELECT ordering by self-reference

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 2005 Forums

Transact-SQL (2005)

SELECT ordering by self-reference

Author

Topic

djpeanut
Starting Member

9 Posts

Posted - 2008-12-04 : 06:19:10

Hi

I am unsure how to explain this but I'll do my best.

I have a table of data representing categories. There is a 'parent' column which contains a reference to the PK of another row if the category is a sub-category, or 0 if it is a top-level category. There is only one level of nesting, so a category can only have a top-level category as it's parent (constrained by the system used to administrate the table).

Each category also has an ordering integer column, which is used to determine its order amongst its group of categories (top-level categories, categories with parent A, categories with parent B, etc)

What I want to do is select a nested list of categories using the ordering integer, so I can build a drop-down list in this kind of format:

- category A
-- sub category A1
-- sub category A2
-category B
-- sub category B1
-- sub category B2

Any ideas would be much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 06:29:58

have a look at recursive CTEs in books online

http://msdn.microsoft.com/en-us/library/ms186243.aspx

or post some sample data

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-04 : 06:44:26

This is much easier than using a CTE.
See this

DECLARE	@Sample TABLE
	(
		ID INT,
		ParentID INT,
		Name VARCHAR(20)
	)

INSERT	@Sample
SELECT	1, NULL, 'Category A' UNION ALL
SELECT	2, NULL, 'Category B' UNION ALL
SELECT	3, 1, 'Sub category A1' UNION ALL
SELECT	4, 1, 'Sub category A2' UNION ALL
SELECT	5, 2, 'Sub category B1' UNION ALL
SELECT	6, 2, 'Sub category B2'

SELECT		Name,
		SIGN(COALESCE(ParentID, 0)) AS IndentLevel
FROM		@Sample
ORDER BY	COALESCE(ParentID, ID),
		Name

E 12°55'05.63"
N 56°04'39.26"

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-12-04 : 07:02:16

I wonder what does that SIGN function do?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2008-12-04 : 07:18:06

SIGN returns a -1, 0, or 1 for negative, zero, or positive numeric values. In Peso's code, if ParentID is null, the COALESCE function will return a 0, meaning no indent level, meaning it's the top element in the hierarchy. All child elements will return a 1.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-04 : 07:39:50

I see now you use zero for top level item and not NULL.

DECLARE	@Sample TABLE
	(
		ID INT,
		ParentID INT,
		Name VARCHAR(20)
	)

INSERT	@Sample
SELECT	1, 0, 'Category A' UNION ALL
SELECT	2, 0, 'Category B' UNION ALL
SELECT	3, 1, 'Sub category A1' UNION ALL
SELECT	4, 1, 'Sub category A2' UNION ALL
SELECT	5, 2, 'Sub category B1' UNION ALL
SELECT	6, 2, 'Sub category B2'

SELECT		Name,
		SIGN(ParentID) AS IndentLevel
FROM		@Sample
ORDER BY	COALESCE(NULLIF(ParentID, 0), ID),
		Name

E 12°55'05.63"
N 56°04'39.26"

djpeanut
Starting Member

9 Posts

Posted - 2008-12-05 : 11:27:27

Hi Peso

Thanks for your suggestion. However, the result set returned has the rows with parent = 0 at the top, and with parent <> 0 beneath. I am looking to generate a nested tree, so having each parent category directly followed by a list of its child categories. There is only 1 level of nesting.

The SQL I use:

SELECT     cat_name, cat_id, cat_order, parent, SIGN(parent) AS indent_level
FROM         tblCategories
ORDER BY COALESCE (parent, cat_id), cat_order

The results I get:

cat_name			cat_id	cat_order	parent	indent_level
Multigyms			5		1			0		0
Cardiovascular		9		2			0		0
Life Fitness		33		1			5		1
Parabody			35		2			5		1
ISO5				42		3			5		1
Compac			44		4			5		1
Ellipticals			29		1			9		1
Recumbent Cycles		28		2			9		1
Rowers			30		3			9		1
Steppers			36		4			9		1

The results I want:


cat_name			cat_id	cat_order	parent	indent_level
Multigyms			5		1			0		0
Life Fitness		33		1			5		1
Parabody			35		2			5		1
ISO5				42		3			5		1
Compac			44		4			5		1
Cardiovascular		9		2			0		0
Ellipticals			29		1			9		1
Recumbent Cycles		28		2			9		1
Rowers			30		3			9		1
Steppers			36		4			9		1

I would really appreciate any help. Cheers.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-05 : 17:03:28

See edit made for post 12/04/2008 : 07:39:50

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources