SQL not exists clause

[Martin Roldan]

Hi,

Having a bit of trouble dealing with not exist clause in a querie.

I have three tables:

Food
designation varchar(255)
quantity integer
type varchar(255)

Meal
no_meal integer
type_meal varchar(255)

Consumed
no_meal integer
designation varchar(255)
quantity integer

Quantity in food is quantity stored and quantity in consumed is consumed quantity so, they will differ and thus natural inner join won't cut it.

Type of food can be 'drink' 'meat' 'fish' and so on. Type of meal can be 'Breakfast' 'Lunch' 'Dinner'

I'll add some data in order to make it clearer.

Food
Steak   100    Meat
Water   200    Drink
Coca cola    300   Drink

Meal
0001     Breakfast
0002     Lunch
0003     Dinner

Consumed
0001     Water    50
0002     Steak    20
0001     Coca cola     20
0003     Water    5
0002     Water    15

I want a querie that will tell me the drinks that have been consumed in all types of meals.

I've been toggling and testing code but can't get it right. At the moment I'm trying this:

SELECT Consumed.designation
FROM Consumed
WHERE NOT EXISTS

(SELECT type_meal
 FROM Consumed, Food, Meal
 WHERE Consumed.designation = Food.designation
 AND Consumed.no_meal = Meal.no_meal
 AND type = 'Drink'
 ORDER BY Food.designation)

EXCEPT

(SELECT type_meal
 FROM Meal);

Now, I want to know which drink has been consumed in every meal, which will only give water.

Any input is appreciated

[NeoPa]

I assume the [Designation] field is what links the [Food] and [Consumed] tables, and that the [No_Meal] field is what links the [Meal] and [Consumed] tables?

[rski]

Maybe like that

select designation from 
(select distinct no_meal,designation from consumed c where designation in (select designation from food where type='Drink')) foo 
group by designation having count(designation)=(select count(*) from meal);