Sub Categories...HELP!

[Arizona]

Hi all!

Ok, I'm trying to complete a shopping site for a design course. The basic problem is this:

On my main product page there are a number of product categories, most of which lead to the list of products, but some categories have sub categories, ie: the kitchen category has crockery, cutlery, glassware etc.

In my database I have a category table and a sub category table but if you click the kitchen link it displays ALL sub categories, which I dont want.

How do I get it to just display the kitchen sub categories. I'm sure this is so simple but my head wont fit around it.

Do i need seperate database tables for each sub category?? I'm sure that isn't the answer cos it seems so long winded but its all my brain will answer at the minute - minly because its fried!

Please help!

[Ubik]

SQL:

Select subcategory.name from maincategory, subcategory where subcategory.maincategorypkey=maincategory.pkey and maincategory.name='kitchen'

[Arizona]

OK, explain this to me like I'm 5 yrs old (I literally have smoke coming from my ears at the moment...) ( Sorry to be a complete numpty )

My database tables are like this:

Main categories table: (Categories)
CategoryID, Category, Pic

Sub Categories table: (Categories2)
Category2ID, Category2, Pic2

So how does your SQL work in that instance. You kinda lost me when there was no inner join mentioned.

Thanks for your help!

[russell_g_1]

i assume there's a bit missing from those tables, you need a foreign key in subcategories don't you? or do you have a link table "between" them?

the sql ubik has given you does do a join and he has assumed you have a foreign key on the subcategory table.