Hi

I have a site that needs the products section broken up into categories and sub categories.

My problem is how to filter the recordset on the sub categories page to only display the sub categories in the category that the user selected in the previous page.

I dont know to much about all this but I can get the page to display all the products in that category but it displays all the products not just one product from each sub category wich is what I want.

DOes the SQL have to look something like this:

SELECT *
FROM tblProducts
WHERE category = 'MMColParam' AND subCategory = 'DINSTINCT'
ORDER BY subCategory DESC

This does not work but am I heading in the right direction?

Any help would be greatly appreciated because I am sooooooooo stuck on this and the dealine is creeping up.

Thanks

David

The way I would do this is:

1. Create a table called TBL_CATEGORIES with the following fields:
CAT_ID - Autonumber
CAT_NAME - Text, 255
CAT_DESC - Memo
CAT_ORDER - Number
CAT_PARENT - Number
CAT_WHATEVER_ELSE - Whatever

Ok, lets say you have 2 categories and 3 sub categories, they would look like this in the database (in the order of the fields listed above):

1, Fishing Rods, All types of fishing rods, 1, <font color=red>0</font id=red>
2, Fishing Reels, Wide range of fishing reels, 2, <font color=red>0</font id=red>
3, Sea Rods, High spec sea rods, 1, <font color=red>1</font id=red>
4, Bass Rods, Quality bass rods, 2, <font color=red>1</font id=red>
5, Fly Fishing Reels, Range of our fly fishing reels, 1, <font color=red>2</font id=red>

Where I have highlighted <font color=red>red</font id=red> above determines the category/sub category they are in.

To show the main categories we filter the database WHERE CAT_PARENT = 0. To get a listing of sub categories in the Fishing Rods Category we filter the database WHERE CAT_PARENT = 1 and so on.

Now we create a products table with the following fields:
PROD_ID - Autonumber
PROD_NAME - Text 255
PROD_CATEGORY - Number
PROD_WHATEVER_ELSE - Whatever else

Say we have 4 products in different categories, here is what we would be looking at in the database:

1, 10ft Bass Rod, <font color=red>4</font id=red>
2, 12ft Sea Rod, <font color=red>3</font id=red>
3, 14ft Sea Rod, <font color=red>3</font id=red>
4, 16ft Sea Rod, <font color=red>3</font id=red>

The category id in red above tells us what category/sub category each product belongs to. So if we filter the product table WHERE PROD_CATEGORY = 3 then we will get all the products that are in the Sea Rods category and none from the Bass Rods category. The results would show back record 2, 3 and 4 but skip 1 because the cateogry ID doesn't equal 3.

That's a basic set up of a relationship database and how to utilise it.

Hope that helps,

Dave

_________________________
WinXP : IIS 5.1 : StudioMX : ASP : VBScript
www.dynamic.ie