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
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") )
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.