Hi to all,
I have Categories and Subcategories in tables tblProdCat and tblProdSubcat respectively and a table with Products in tblProds and in tblProds I have foreign keys for Categories and Subcategories.

tblProdCat - is my source for my main menu
tblProdSubcat - is my source for submenu
also the tblProdSubcat has a foreign key for the tblProdCat.

Imagine a 2 column layout webpage. At left, the main menu and a on the center top the submenu going horizontally.

The main menu passes the variable "cat" via link to the same page an and makes the submenu shows all the submenu items that are equal to the "cat" variable - which is the id of the foreign key of the tblProdCat

my query is as follows(just the main menu and submenu)

Main menu:

<
%Dim rsCats
Dim rsCats_cmd
Dim rsCats_numRows

Set rsCats_cmd = Server.CreateObject ("ADODB.Command")
rsCats_cmd.ActiveConnection = MM_connProducts_STRING
rsCats_cmd.CommandText = "SELECT * FROM tblProdCat WHERE bitShow = True ORDER BY txtProdCat" 
rsCats_cmd.Prepared = true

Set rsCats = rsCats_cmd.Execute
rsCats_numRows = 0
%>

Submenu:

<
%Dim rsSubcats__param
rsSubcats__param = "0"
If (Request.QueryString("cat") <> "") Then 
  rsSubcats__param = Request.QueryString("cat")
End If
%>
<
%Dim rsSubcats
Dim rsSubcats_cmd
Dim rsSubcats_numRows

Set rsSubcats_cmd = Server.CreateObject ("ADODB.Command")
rsSubcats_cmd.ActiveConnection = MM_connProducts_STRING
rsSubcats_cmd.CommandText = "SELECT intPCatID, intPSubCatID, scfirst, txtSubCatName, intProdCatID FROM tblProdSubcat, tblProdCat WHERE intPCatID = intProdCatID AND intPCatID = ? ORDER BY txtSubCatName ASC" 
rsSubcats_cmd.Prepared = true
rsSubcats_cmd.Parameters.Append rsSubcats_cmd.CreateParameter("param1", 5, 1, -1, rsSubcats__param) ' adDouble

Set rsSubcats = rsSubcats_cmd.Execute
rsSubcats_numRows = 0
%>

All works well until here.
Now both menus have a purpose and it is to show the product information store on tblProds

The query is as follows:

<
%Dim rsProducts__param
rsProducts__param = "%"
If (Request.QueryString("cat")  <> "") Then 
  rsProducts__param = Request.QueryString("cat") 
End If
%>
<
%Dim rsProducts__param1
rsProducts__param1 = "%"
If (Request.QueryString("subcat")  <> "") Then 
  rsProducts__param1 = Request.QueryString("subcat") 
End If
%>
<
%Dim rsProducts
Dim rsProducts_cmd
Dim rsProducts_numRows

Set rsProducts_cmd = Server.CreateObject ("ADODB.Command")
rsProducts_cmd.ActiveConnection = MM_connProducts_STRING
rsProducts_cmd.CommandText = "SELECT tblProds.intPCatID, intProdID, intSCatID, textProdName, txtProdCode, intProdCatID, intPSubCatID FROM tblProds, tblProdCat, tblProdSubcat WHERE tblProds.intPCatID = intProdCatID AND tblProds.intSCatID = intPSubCatID AND intProdCatID LIKE ? AND intPSubCatID LIKE ? AND tblProds.bitShow =True ORDER BY intSCatID ASC " 
rsProducts_cmd.Prepared = true
rsProducts_cmd.Parameters.Append rsProducts_cmd.CreateParameter("param1", 200, 1, 255, rsProducts__param) ' adVarChar
rsProducts_cmd.Parameters.Append rsProducts_cmd.CreateParameter("param2", 200, 1, 255, rsProducts__param1) ' adVarChar

Set rsProducts = rsProducts_cmd.Execute
rsProducts_numRows = 0
%>

As you can see the query calls for products that are like/equal to param and param1. (cat and subcat)

This works ok. but I'm not accomplishing what I want and is to show All the submenu items that fall on a category but only show the products that are on the first subcategory.

Example:
There is a main category called compressors when I click on that item, I pass the value cat that is processed and returns All the items on the submenu related to compressors. In this case Compressors, Accesories, Moisture Pressure Control and Ultimate Moisture Control

The products however should only display the first subcategory: Compressors

How do I accomplish that? I have been pulling my hair trying to solve this. I think the way it should be would be something like:

pass the category variable cat (category ID)
but on the Query to pull the products should have a som kind of join or nested query pulling only the items that are on the main category and belong to the first subcategory on that main category.

Right now I can pull all the products that fall on the main category - but I only want the items from the first subcategory. So once there I can create another link from the submenu to retrieve the other products.

Any help would be much appreciated. Sorry for the long explanations. I hope I'm making sense as well. I'm desperate.

Javier