SQL not exist issue

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

I have three tables:

Meal

no_meal integer
type_meal varchar(255)

Consumed

no_meal integer
designation varchar(255)
quantity integer

Food

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
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_me开发者_Python百科al
 FROM Meal);

How the hell do I get it right?

Thanks for input.

EDIT:

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

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

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.

Now I understand what you want...

SELECT designation
FROM Food
WHERE designation IN (
    SELECT designation 
    FROM Consumed
    GROUP BY designation
    HAVING Count(DISTINCT no_meal) = (
        SELECT Count(*) FROM Meal
    )
)
WHERE type = 'Drink'

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

Your edit did clear things up. Note that following query will not work if the combination Designation, Typein Consumedis not unique but I'm guessing it will be.

SELECT  
    c.Designation
FROM
    (SELECT  
         c.Designation
     FROM    
         Consumed c
     INNER JOIN 
         Meal m ON m.no_meal = c.no_meal
     GROUP BY
         c.Designation
     HAVING  
         COUNT(*) = (SELECT COUNT(*) FROM Meal)) c
INNER JOIN 
    Food f ON f.Designation = c.Designation
WHERE
    f.Type = 'Drink'                  

select *
from food f inner join
     consumed c_1 on c_1.designation = f.designation
where type='Drink' and
not exists -- to filter out drinks that are not a part of all the meals
(select 1 
from consumed c_2 inner join
     meal m_1 on m_1.no_meal = c_2.no_meal
where c_1.no_meal = c2.no_meal and
      c_1.designation = c2.designation and
 not exists (select 1 
               from meal m_2
              where m_1.no_meal = m_2.no_meal))

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

Here is the model structure and insertions you suggested:

USE SqlTests
GO

CREATE TABLE Meal (
    no_meal int
    , type_meal nvarchar(255)
)
GO

insert into Meal (no_meal, type_meal)
    values (1, N'Breakfast')
GO
insert into Meal (no_meal, type_meal)
    values(2, N'Lunch')
GO
insert into Meal (no_meal, type_meal)
    values(3, N'Dinner')
GO

CREATE TABLE Consumed (
    no_meal int
    , designation nvarchar(255)
    , quantity int
)
GO

insert into Consumed (no_meal, designation, quantity)
    values (1, N'Water', 50)
GO
insert into Consumed (no_meal, designation, quantity)
    values (2, N'Steak', 20)
GO
insert into Consumed (no_meal, designation, quantity)
    values (1, N'Coca cola', 20)
GO
insert into Consumed (no_meal, designation, quantity)
    values(3, N'Water', 5)
GO
insert into Consumed (no_meal, designation, quantity)
    values(2, N'Water', 15)
GO

CREATE TABLE Food (
    designation nvarchar(255)
    , quantity int
    , [type] nvarchar(255)
)
GO

insert into Food (designation, quantity, [type])
    values (N'Water', 200, N'Drink')
GO
insert into Food (designation, quantity, [type])
    values (N'Steak', 100, N'Meat')
GO
insert into Food (designation, quantity, [type])
    values (N'Coca cola', 200, N'Drink')
GO

Then, selecting with the proposed SELECT:

select c.designation
    from Consumed c
        inner join Meal m on m.no_meal = c.no_meal
        inner join Food f on f.designation = c.designation and f.[type] LIKE N'Drink'
    group by c.designation 
        having COUNT(c.designation) = (select COUNT(type_meal) from Meal)

Returns (at least on my database engine (SQL Server Express 2005)

Water

Is my information data correct?