Please help me debug my SQL query.

Question

I have a query:

Select n_portions, dish_name
from food_order, dish
where  n_portions= 
(select max (n_portions) 
 FROM food_order);

It's meant to return:

fish pie 3
steak and chips 1
pasta bake 2
stuffed peppers 1

But i get:

Pasta bake      35
Fish pie        35
Steak and chips 35
Stuffed peppers 35
Ham and rice    35
Lamb curry      35

Why is this happing?

table data
table data
Insert into customer_order values ('00001', '03-Apr-09', '07-apr-09','St. Andrew St'); 
Insert into customer_order values ('00002', '05-Apr-09', '01-May-09', 'St. Andrew St');
Insert into customer_order values ('00003', '12-Apr-09', '27-Apr-09', 'Union St');
Insert into customer_order values ('00004', '12-Apr-09', '17-Apr-09', 'St. Andrew St');

Insert into Dish values ('D0001', 'Pasta bake',      'yes', '6.00'); 
Insert into Dish values ('D0002', 'Fish pie',        'no',  '9.00');  
Insert into Dish values ('D0003', 'Steak and chips', 'no',  '14.00');   
Insert into Dish values ('D0004', 'Stuffed peppers', 'yes', '11.50');   
Insert into Dish values ('D0005', 'Ham and rice'   , 'no',  '7.25');  
Insert into Dish values ('D0006', 'Lamb curry'     , 'no',  '8.50'); 

Insert into Drink values ('DR0001', 'Water',  'soft',      '1.0');
Insert into Drink values ('DR0002', 'Coffee', 'hot',       '1.70');
Insert into Drink values ('DR0003', 'Wine'  , 'alcoholic', '3.00'); 
Insert into Drink values ('DR0004', 'Beer'  , 'alcoholic', '2.30');  
Insert into Drink values ('DR0005', 'Tea'   , 'hot'     ,  '1.50');   

Insert into food_order values ('F000001', '000001', 'D0003', '6');
Insert into food_order values ('F000002', '000001', 'D0001', '4');
Insert into food_order values ('F000003', '000001', 'D0004', '3');
Insert into food_order values ('F000004', '000002', 'D0001', '10');
Insert into food_order values ('F000005', '000002', 'D0002', '10');
Insert into food_order values ('F000006', '000003', 'D0002', '35');
Insert into food_order values ('F000007', '000004', 'D0002', '23');

Insert into drink_order values ('D000001', '000001', 'DR0001', '13');
Insert into drink_order values ('D000002', '000001', 'DR0002', '13');
Insert into drink_order values ('D000003', '000001', 'DR0004', '13');
Insert into drink_order values ('D000004', '000002', 'DROOO1', '20');
Insert into drink_order values ('D000005', '000002', 'DR0003', '20');
Insert into drink_order values ('D000006', '000002', 'DR0004', '15');
Insert into drink_order values ('D000007', '000003', 'DR0002', '35');
Insert into drink_order values ('D000008', '000004', 'DR0001', '23'); 
Insert into drink_order values ('D000009', '000004', 'DR0003', '15');
Insert into drink_order values ('D0000010', '000004', 'DR0004', '15');

Answer 1

How are "food_order" and "dish" related? You don't seem to specify any relationship between the two tables in your query..... if you want the maximum for each dish, you need to max just the values for that particular dish - right now, you're just retrieving the max over all entries in the table.

Just assuming here (without knowing), you probably need something like:

Select 
  n_portions, dish_name
from 
  food_order, dish
where  
   n_portions = 
     (select max (n_portions) FROM food_order f2 WHERE f2.dish# = dish.dish#)

Answer 2

you are setting the value of n_portions to the max n_portions of food_order in the subselect.

If you are trying to get the max for each( not all), you need to do a count of n_portions and group by dish_name. Also, you are missing the joins between food_order and dish.

Answer 3

select dish_name, max(n_portions)
from       food_order f
inner join dish       d on d.dish_id = f.dish_id

Answer 4

You can use a join and an aggregate/group by instead of a subquery:

SELECT MAX(n_portions), dish_name
FROM food_order 
INNER JOIN dish ON (food_order.dish = dish.dish) --guessing a bit here
GROUP BY dish_name

Answer 5

The problem stems from the fact that there is no join between food _ order and dish in the sub select. Thus it will always simply return the max of n_ portions in food_order - the same value every time.

From the information provided it is hard to say exactly what you're looking for however, but suffice to say you need to have some filter on the sub select (the select on the max). Something like...

Select fo.n_portions, d.dish_name
from food_order fo, dish d
where  fo.n_portions= 
(select max (n_portions) 
 FROM food_order fo where food_order.dish_id = d.dish_id);