Query woes

  • Thread starter Thread starter Dale Palmer
  • Start date Start date
D

Dale Palmer

I'm having trouble trying to get a Select Statement working, my current
statement is attached below.

I have two tables: Table one (tblProducts) is product description & Table
two (tblExtraProd) is a list of ingredients for each product.

I need to be able to select and ingredient that needs to be included and one
that needs to be excluded and get a result.

Any help or ideas would be very much appreciated.

Regards

Dale

SELECT seasonedpi.dbo.tblProducts.P_ID,
seasonedpi.dbo.tblProducts.DISPLAYNAME, seasonedpi.dbo.tblProducts.Cuisine,
seasonedpi.dbo.tblProducts.PType,
seasonedpi.dbo.tblProducts.mild, seasonedpi.dbo.tblProducts.sultry,
seasonedpi.dbo.tblProducts.spicy,
seasonedpi.dbo.tblProducts.explosive,
seasonedpi.dbo.tblProducts.volcanic, seasonedpi.dbo.tblExtraProd.ExProduct
FROM seasonedpi.dbo.tblProducts RIGHT OUTER JOIN
seasonedpi.dbo.tblExtraProd ON
seasonedpi.dbo.tblProducts.P_ID = seasonedpi.dbo.tblExtraProd.ExRelate
WHERE (seasonedpi.dbo.tblExtraProd.ExProduct <> 30) AND
(seasonedpi.dbo.tblExtraProd.ExProduct = 135)
 
I think that you are trying to ask the following question of the data:

What product(s) includes ingredient X, but does not include ingredient Y.

If this is the case, then you need to apply some Set Theory
(http://plato.stanford.edu/entries/set-theory/) to the problem, and solve it
with 2-4 queries.(Probably less if you understand subqueries)

First find the set of data that includes Y. (The bad set, booo)
Next find the set of data that includes X. (The good set, Yeah!)

Link X and Y in a query and find those in X that are not in Y. This will
leave you with the Pure X group.

Drawing a Venn Diagram may help.
 
Back
Top