Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-08-24 : 19:39:28
|
Amanda writes "I have a table that looks like this:
ID
storeID
itemID
itemDescription
itemCost
The desired return set is a list of storeIDs that have BOTH item x within price range x1-x2 AND
item y within price range y1-y2. To make things worse, there may be up to 6 such items,
each with their own price range.
If you try "select storeID where
(itemID=x and itemCost between x1 and x2)
AND (itemID=y and itemCost between y1 and y2)"
you would obviously always get an empty recordset.
IF you do the above with OR in place of AND, you get stores with one or the other, but not
necessarily both. This is the union of the sets, not the intersection.
I have tried listing the table more than once in the from table list. I have tried doing select storeID where (itemID=x and itemCost between x1 and x2) AND storeID in (select storeID where (itemID=y and itemCost between y1 and y2)) but that will time out and would certainly not
scale to 6 items even if two items didn't time out. I have tried combinations of EXISTS, HAVING, and ALL with no luck.
HELP!
I am using SQL7, NT SP5" |
|
|
|
|
|
Union of sets with n criteria on the same column