I have a database with products that contain a category field. I can list the products in any category using:
SELECT ProductName, ItemNum, Description, RetailPrice
FROM
Requires Free Membership to View
Products
WHERE Category = Request.QueryString("ID")
The problem is that for certain customers, I need to include one or two extra categories to the category list, called "special" and "hot." Some customers never see these categories. I also need to have products show up in the special and hot categories that are the same products that are already in other categories. The products that need to go into these extra categories have a "1" in a field called "special_category" for the special list and a "1" in a field called "hot_category" for the hot list. A product can show up in its own category, the special category, or the hot category, or a combination.
The answer for your design is pretty straightforward. Try this:
select Category
, ProductName
, ItemNum
, Description
, RetailPrice
from Products
where Category = Request.QueryString("ID")
union all
select 'Special'
, ProductName
, ItemNum
, Description
, RetailPrice
from Products
where special_category = 1
union all
select 'Hot'
, ProductName
, ItemNum
, Description
, RetailPrice
from Products
where hot_category = 1
You may want to consider redesigning your tables to allow a product to belong to more than one category naturally:
Categories CategoryID CategoryName Products ProductID ProductName ItemNum Description RetailPrice ProductCategories ProductID CategoryID
There is one row in ProductCategories for each category that a product belongs to. Special and Hot are then two categories just like all the others (structurally). If Special and Hot had CategoryID values 23 and 37 respectively, then your query would be
select Category
, ProductName
, ItemNum
, Description
, RetailPrice
from Products
where Category in ( 23, 37
, Request.QueryString("ID") )
This was first published in February 2004
Join the conversationComment
Share
Comments
Results
Contribute to the conversation