Building A Query With A Criteria Of Many

[Guest]

Table: Stores
StoreID
StoreName
StoreInfo

Table: Products
ProductID
ProductName

Table: StoreProducts
SP_ID
SP_R_StoreID
SP_R_ProductID


Question: Need a query that will look up the store that sells 5 particular
products and nothing else from the table StoreProducts. Can this be done in
a simple SELECT query? If not what kind of query will work for this scenario?

 

[Dale Fye]

Try (untested):

SELECT S.StoreID, S.StoreName, Count(SP.SP_R_ProductID) as
NumOfProdsFromList
FROM Stores S INNER JOIN StoreProducts SP
ON S.StoreID = SP.SP_R_StoreID
WHERE SP.SP_R_ProductID IN (ProdID1, ProdID2, ProdID3, ProdID4, ProdID5)
GROUP BY S.StoreID, S.StoreName
HAVING Count(SP.SP_R_ProductID) = 5

You shouldn't need the Count in the select statement, it is just there to
help you visualize what the rest of the query is doing