MySQL structure and efficiency question

[ViaoV]

I have a question about keeping things efficient, as far as a database structure goes. I need to set up a page which will list several items orginized by catagory/sub catagory.  The logical idea would be to setup 3 tables as follows:

 

catagoryTable
-catagoryId
-catagoryName
-parent catagory (could be 0 if its a toplevel catagory)

itemTable
itemName
catagoryId

 

However all items are going to be displayed at the same time. So to accomplish this I'm going to have to make a call to get a list of parent categories, call each one to get a list of subcategories, then call each sub category to get a list of items. The categories have to be editable and the items have to be printed out by category. I have been trying to think of something like using DISTINCT somehow to not have to loop through so many queries. The category numbers are small like 10 parent, 1-5 sub in each, 1-15 items in each. The page will get slightly heavy use though so I was wondering if anyone knows a way to make something like that more efficient.

[fenway]

Yes, you'd need a JOIN for each of the N levels of the hierarchy that you'd want to display... provided that N is small (<5), you're ok.

[ViaoV]

Could you give me a query example? not sure how I would join em

[fenway]

It's just a self join to the original table using parentID as the join condition.