Searching link tables

[AnotherMuggle]

I have a link table that contains meals, with their ingredients.

For example:

beans on toast (mealID 1):

beans (ingredientID 1)
bread (ingredientID 2)
butter (ingredientID 3)

The table looks like the following

mealID  ingredientID
1         1                   
1         2
1         3

What SQL statement can I use to return any meals that do not contain a specific ingredient?

So if for example I wanted to return meals that do not contain butter (ingredientID 3), what do I do? At the moment, the meal is still returned because it is returning the result from a tuple that is not butter, i.e. bread of beans.

Thanks,
TK

[chazzy]

maybe...

select * from meals
where mealID not in (select mealID from meal_ingredients where ingredientID=3)

[AnotherMuggle]

maybe...

select * from meals
where mealID not in (select mealID from meal_ingredients where ingredientID=3)

Thanks.

But how do I do that, if there are many ingredientIDs that I want to make sure are not in the result set?

At the moment I have a very large array with ingredientIDs that I want to exclude from the meal results.

[chazzy]

change ingredientID=3 to ingredientID in (list of values), where you programmaticly create the list of values as a comma separated list.

[AnotherMuggle]

change ingredientID=3 to ingredientID in (list of values), where you programmaticly create the list of values as a comma separated list.

Nicely done.

Thanks once again