Having a bit of trouble dealing with not exist clause in a query.
I have three tables:
WHERE NOT EXISTS
FROM Consumed, Food, Meal
WHERE Consumed.designation = Food.designation
AND Consumed.no_meal = Meal.no_meal
AND type = 'Drink'
ORDER BY Food.designation)
Steak 100 Meat
Water 200 Drink
Coca cola 300 Drink
0001 Water 50
0002 Steak 20
0001 Coca cola 20
0003 Water 5
0002 Water 15
Twist your mind a little bit, and think with double negatives ....
I want [...] the drinks that have been consumed in all types of meals.
You want all drinks for which there are NO meal types for which that drink is NOT part of the meal type:
select distinct f.designation from food f where type = 'Drink' and not exists ( select * from meal m1 where not exists ( select * from meal m2 join consumed c on c.no_meal = m2.no_meal where m1.no_meal = m2.no_meal and c.designation = f.designation ) )
This is called relational division.