Query has me stumped!

• • Reply

  18 years ago

  by cheesekeeper

  Robert J. Walker

  Salt Lake City, Utah, USA, United States

  Joined 18 years ago

  I'm developing a site in ASP which allows the user to perform a search on items arranged in categories. For simplicity's sake, I'll strip down the problem to only the parts involved in the difficulty I'm having. There are three tables in question:
  

  
  1) Items table: Contains all the items that can be searched. Each item has a unique int identifier, itemID.
  2) Categories table: Contains all of the categories to which the items can belong. Each category has a unique int identifier, catID.
  3) ItemCategories table: Relationship table between the items and categories. Contains two int columns, itemID and catID.
  

  
  Each item can belong to zero or more categories, and categories can have zero or more items belonging to each.
  

  
  So here's the difficulty: The user needs to be able to search for items in various ways, including selecting one or more existing categories to which the resulting items must belong. They must also have the ability to specify that the items returned must belong to at least one of the categories or all of them.
  

  
  So I'm left with the task of trying to develop two queries which will do this. I came up with one that seems to work for the "at least one" case:
  

  
  

  Code:

  SELECT DISTINCT I.itemID
  FROM Items I, ItemCategories IC
  WHERE I.itemID = IC.itemID
  AND (IC.catID = [first category]
  OR IC.catID = [second category]
  ...
  OR IC.catID = [last category])

  
  

  
  Changing the ORs to ANDs, however, does not work for the "all" case (which makes sense when I think about it), and I've been puzzling over how to do that for the last couple of days. Any ideas?
  

  
  Thanks in advance.

  Report abuse