Results 1 to 1 of 1
May 20, 2011, 20:46 #1
- Join Date
- Aug 2004
- 0 Post(s)
- 0 Thread(s)
Query problem retrieving data from 2 tables
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)
<% 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 %>
<% 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.
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.