? getting categories and all their subcategories s

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)

? getting categories and all their subcategories s

Author

Topic

samoyed
Starting Member

2 Posts

Posted - 2009-06-23 : 16:32:25

hello to all,
i have a categories table which follows this pattern:

id (int)
name (varchar)
parentid (int)

i want to get a list of all zero level categories (those with parentid = null ) and their child categories - just 1 level down.

this is accomplished using:

SELECT id, name, parentid
FROM categories
WHERE (parentid IS NULL) OR
(parentid IN
(SELECT id
FROM categories AS categories_1
WHERE (parentid IS NULL)))

BUT.. the list i get is sorted in such away that first come the entries for all the parent categories followed by all the child categories.
what i want is to get the list in this manner:

parent1
-child of parent1
-child of parent1
-child of parent1
parent2
-child of parent2
-child of parent2

and so on..

is this possible? if so - how?
i'm a newbie to sql so forgive me if this is trivial stuff.
any help would be highly appreciated

Heinduplessis
Starting Member

23 Posts

Posted - 2009-06-24 : 08:23:27

You'll need to carry the relationship in the result set in order to sort by it.

Maybe add Order by ParentID, id - it might work.

However, I always use a treelevel and a treecode

TreeLevel indicates how far down in the category the node is, ie 0 for root, 1 for level 1 etc.

TreeCode is a trail of ids of parents and grandparents etc of each node, surrounded by /, ie:

/1/6/8/

This means 8's parent is 6 and 6's parent is 1.

Then you can just add

order by TreeCode and it will sort itself according to grandparents.

Of course, some extra code is then required to maintain the treecode, but it's utterly worth it, queries become so simple.