"not in" query- type & flavor not ordered

[machumpion]

I have an Orders table and a Products table. Orders has the field StoreName, ProductID, Revenue, and Date.

Product has ProductID, Type and Flavor. Type can be 'mint' or 'gum', flavor can be 'cherry', 'lime', 'peppermint'. Each Type of product has each of the three flavors.

How can I write a query asking which permutations of Type and Flavor have not ever been ordered by StoreName s?

Thanks!

 

[sneuberg]

If there is a ProductID for each Type and Flavor isn't the question simply what ProductIDs the Store has never ordered?

 

[machumpion]

If there is a ProductID for each Type and Flavor isn't the question simply what ProductIDs the Store has never ordered?

since the records is comprised of data from different vendors, there are some unique productIDs for the same type and flavor

i.e. Mint Lime vs MintLime

 

[arnelgp]

SELECT order.Storename, product.ProductID
FROM product, [order] where (order.[storename] & product.[productid]) not in (SELECT t1.[storename] & t1.[productid] AS expr
FROM [order] as t1);

 

[machumpion]

with a ton of edits, i managed to figure it out using your code Arnelgp. Thank you!