Category sorting problem

[theS70RM]

Hi, I have a table thats laid out something like the following....

cat_id, cat_name,    cat_parent
    1   furniture     null
    2   electrical    null
    3   tvs           2
    4   desks         1
    5   corner desks  4       
    6   sony tvs      3
    7   wardrobe      1

so categories are all stored in one table with sub categories. Then I have another table with lots of items that belong to any one of the categories i.e. "my tv" cat_id = 6.

My question is, how can I easily return a list of all items sorted alphabetically by category & subcategory? Is there a away to recurse through the categories just with sql, so it finds the parent category for each item and sorts by that?

I want to keep the category table in this format so it is dynamic and as many sub categories as possible can be added.

Thanks for any help!


Andy.

[rpnew]

Hi,
I understood your problem a bit not much. can you explain with example what type of output you want exactly??

Regards,
RP

[ronverdonk]

Now do you want a list of all categories (table shown) with all subcategories listed or a list of all subcategories with all categories that each one belongs to?

Ronald