By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,650 Members | 753 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,650 IT Pros & Developers. It's quick & easy.

SQL not exists clause

P: n/a
Hi,

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

I have three tables:
Expand|Select|Wrap|Line Numbers
  1. Food
  2. designation varchar(255)
  3. quantity integer
  4. type varchar(255)
  5.  
  6. Meal
  7. no_meal integer
  8. type_meal varchar(255)
  9.  
  10. Consumed
  11. no_meal integer
  12. designation varchar(255)
  13. 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.

Expand|Select|Wrap|Line Numbers
  1. Food
  2. Steak   100    Meat
  3. Water   200    Drink
  4. Coca cola    300   Drink
  5.  
  6. Meal
  7. 0001     Breakfast
  8. 0002     Lunch
  9. 0003     Dinner
  10.  
  11. Consumed
  12. 0001     Water    50
  13. 0002     Steak    20
  14. 0001     Coca cola     20
  15. 0003     Water    5
  16. 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:

Expand|Select|Wrap|Line Numbers
  1. SELECT Consumed.designation
  2. FROM Consumed
  3. WHERE NOT EXISTS
  4.  
  5. (SELECT type_meal
  6.  FROM Consumed, Food, Meal
  7.  WHERE Consumed.designation = Food.designation
  8.  AND Consumed.no_meal = Meal.no_meal
  9.  AND type = 'Drink'
  10.  ORDER BY Food.designation)
  11.  
  12. EXCEPT
  13.  
  14. (SELECT type_meal
  15.  FROM Meal);
Now, I want to know which drink has been consumed in every meal, which will only give water.

Any input is appreciated
Nov 25 '10 #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,470
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?
Nov 25 '10 #2

Expert 100+
P: 700
Maybe like that
Expand|Select|Wrap|Line Numbers
  1. select designation from 
  2. (select distinct no_meal,designation from consumed c where designation in (select designation from food where type='Drink')) foo 
  3. group by designation having count(designation)=(select count(*) from meal);
  4.  
Nov 28 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.