Martin Martin - 1 year ago 49
SQL Question

SQL not exist issue

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

I have three tables:


no_meal integer
type_meal varchar(255)


no_meal integer
designation varchar(255)
quantity integer


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

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 want a query 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

(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)


(SELECT type_meal
FROM Meal);

How the hell do I get it right?

Thanks for input.


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


Steak 100 Meat
Water 200 Drink
Coca cola 300 Drink


0001 Breakfast
0002 Lunch
0003 Dinner


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

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

Hope I cleared some minds about the problem

Answer Source

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 
  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
        m1.no_meal = m2.no_meal and
        c.designation = f.designation

This is called relational division.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download