CREATE TABLE Products(ProductID INT, Description VARCHAR(50)) -- all the different productsCREATE TABLE ProductIngredients (ProductID INT, IngredientID INT) -- links ingredients to productsCREATE TABLE Ingredients (IngredientID INT, Description VARCHAR(50)) -- all different ingredientsCREATE TABLE ProductionRuns (ProductionRunID INT, ProductID INT) -- details on the different 'productionruns'CREATE TABLE ProductionRunDetails (ProductionRunID INT, IngredientID INT) -- which ingredients were used in the runsINSERT Products (ProductID, Description) VALUES (1,'Cooked Shrimp')INSERT Products (ProductID, Description) VALUES (2,'Shrimp in Brine')INSERT Ingredients (IngredientID, Description) VALUES (1,'Class A shrimp')INSERT Ingredients (IngredientID, Description) VALUES (2,'Class B shrimp')INSERT Ingredients (IngredientID, Description) VALUES (3,'Class C shrimp')INSERT Ingredients (IngredientID, Description) VALUES (4,'Brine')INSERT Ingredients (IngredientID, Description) VALUES (5,'Salt')INSERT Ingredients (IngredientID, Description) VALUES (6,'Liquor')INSERT ProductIngredients (ProductID, IngredientID) VALUES (1,1)INSERT ProductIngredients (ProductID, IngredientID) VALUES (1,5)INSERT ProductIngredients (ProductID, IngredientID) VALUES (2,1)INSERT ProductIngredients (ProductID, IngredientID) VALUES (2,2)INSERT ProductIngredients (ProductID, IngredientID) VALUES (2,4)INSERT ProductionRuns (ProductionRunID, ProductID) VALUES (100,1)INSERT ProductionRuns (ProductionRunID, ProductID) VALUES (101,1)INSERT ProductionRuns (ProductionRunID, ProductID) VALUES (102,1)INSERT ProductionRuns (ProductionRunID, ProductID) VALUES (103,2)INSERT ProductionRuns (ProductionRunID, ProductID) VALUES (104,2)INSERT ProductionRunDetails (ProductionRunID, IngredientID) VALUES (100,1)INSERT ProductionRunDetails (ProductionRunID, IngredientID) VALUES (100,5)INSERT ProductionRunDetails (ProductionRunID, IngredientID) VALUES (103,1)INSERT ProductionRunDetails (ProductionRunID, IngredientID) VALUES (103,2)INSERT ProductionRunDetails (ProductionRunID, IngredientID) VALUES (103,4)INSERT ProductionRunDetails (ProductionRunID, IngredientID) VALUES (104,1)INSERT ProductionRunDetails (ProductionRunID, IngredientID) VALUES (104,3)INSERT ProductionRunDetails (ProductionRunID, IngredientID) VALUES (104,6)
We produce a series of products. The ingredients for every product arestored in the ProductIngredients table. These are the preffered ingredients,due to different reasons on the productionfloor someone may decide to usea different ingredient for a certain product. I need a query that returns the ProductionRunIDs in which were used the 'wrong' ingredients.In my sampledata that would be ProductionRunID 104. I can think of a solution looping through the ProductionRuns but I'm almost certain there's a 'set-based' way of doing this.Any input is much appreciated.Peter 
Difference between sets of data
Posted - 2003-01-09 : 05:15:50
But Peter, I have to say THANK YOU for posting such good DDL and DML for us to play with.If everyone did that, solving problems on SQLTeam would be SO much easier.Damian 
.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.