Strange query needed

[Viru]

Hi Friends,

I need a very strange SQL query.
I have table structure like

cat_id, cat_name, cat_parent_id

Each cat has subcategories and those subcategories can have subcategories means

1, Cat1, 0
2, Cat2, 0
3, Cat3, 2
4, Cat4, 2
5, Cat5, 4

So Cat1 >> Cat2 >> Cat3

Now , I need a list of cat, subcat and subcat's subcat in a single query. e.g.
If I need all subcat of cat 2 then
result should be

2,3,4,5

Is it possible in a single query?
thanks.

[Viru]

I have found it.

Ok I am giving the link for others to refer.

http://www.4guysfromrolla.com/webtech/071906-1.shtml

Thanks

[rmiao]

Thank you for sharing info.

[jcelko]

>> I have table structure like
cat_id, cat_name, cat_parent_id <<

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it.

Next, please read and learn ISO-11179 data element naming conventions. There is no such thing as a "cat_id"; an attribute can be an identifier or a category, but never both. Think about it.

Finally, get a copy of TREES & HIERARCHIES IN SQL and/or Google "nested sets model" for a very easy, very fast solution. You are still thinking that DDL and DML are separate things instead of part of a whole.




Each cat has subcategories and those subcategories can have subcategories means