Q

Special categories

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 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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close