Help With Stored Proc to retrieve Hierarchical Data 1

[Kalisto]

I have an existing Database with 2 tables represented as follows

table productCategories

pKey, Description, DisplayOrder, ... (other fields elided)

table productCategoryRelationships
parentKey, childKey

The aim of these tables is to allow a category to have sub categories on a 0 to many basis, and also allow cross linking of categories.

I want to retrieve the data such that I can get each category, with any child categories afterwards. The main issue I have is that the Display Order, as I want to group the records so that I get Parent, Child nodes in order, next parent and so on.

so if I have the data like

productCategories

1, category1, 1, ...
2, category2, 2, ...
3, category3, 3, ...
4, category4, 4, ...
5, category2_1, 1, ...
6, category2_2, 2, ...


productCategoryRelationships
2,5
2,6

I can get the data out like so;
1, category1, 1, ...
2, category2, 2, ...
5, category2_1, 1, ...
6, category2_2, 2, ...
3, category3, 3, ...
4, category4, 4, ...

The best I have so far is ;

select * from productCategories as c
left outer join productCategoryRelationships as r
on r.parentkey = c.pKey

left outer join productCategories as c2
on c2.pkey = r.childKey

order by c.order, c2.order

the only issue with this is that my code is picking up the order from all the records from c first, so I get

1, category1, 1, ...
5, category2_1, 1, ...
2, category2, 2, ...
6, category2_2, 2, ...
3, category3, 3, ...
4, category4, 4, ...

So how do I resolve this, or do I need to get the database redesigned?

 

[hmckillop]

A handy way of doing this in SQL 2005 is using common table expressions.

Rather than re-invent the wheel have a look at

http://blogs.msdn.com/simonince/archive/2007/10/17/hierarchies-with-common-table-expressions.aspx

Its very similiar to what you are trying to do.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Kalisto]

Im using sql2008 and I cant get it right, I might just can this and do it in code on the front end, as much as I'd prefer to do it in Sql, and also to learn how it is done, I dont think my boss will give me th eluxury of time to learn this technique..