Infinite Category Depth - 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)

Infinite Category Depth

Author

Topic

jon3k
Starting Member

49 Posts

Posted - 2005-02-04 : 18:45:50

I've setup a table called category:

id - int, primary key, yadda yadda
parent_id = int
depth = int
cattitle = varchar(50)

And I've written a query that will take each record, and via a reflexive relationship, join each record to its parent:

SELECT category.cattitle, subcat.cattitle FROM category INNER JOIN category subcat ON subcat.parent_id = category.id

And this all works fine and dandy, except that there's no useful and efficient way to output the results. It simply looks at each record and joins it to its parent.

What would be the proper grouping/ordering to get a nice clean outputtable (is that a word?) result set?

I'd like
Category1
--SubCat1
----SubSubCat1
Category2
--SubCat2
----SubSubCat2
----SubSubCat2
Category3
--SubCat3
--SubCat3

Etc, etc.

Anyone ever pulled this off before? Is there a better way to do this? I'd really like to do it with one query, and output it in a single pass, without having to loop over the result set several times.

(mysql and php by the way).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-04 : 18:52:02