Ask the Expert

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

    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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: