Need help with a query

[andylbh]

I have to:

Find the distinct names of all diners who ordered 2 or more portion of the same foods on the same day in the same restaurant.

SQL> select * from foodorders order by diner;

DINER                          RESTAURANT                     ODATE     FOOD
------------------------------ ------------------------------ --------- -------------------
ALICE                          BEST FOOD                      09-APR-11 BAKED SALMON
ALICE                          BEST FOOD                      09-APR-11 GRILLED FISH
ALICE                          LONGEST BEACH                  29-APR-11 BAKED SALMON
ALICE                          LONGEST BEACH                  29-APR-11 PEPPER CRAB
BOB                            BY THE BEACH                   29-APR-11 BAKED SALMON
BOB                            BY THE BEACH                   29-APR-11 CHILI CRAB
BOBBY                          BY THE BEACH                   28-APR-11 BAKED SALMON
BOBBY                          BY THE BEACH                   28-APR-11 GRILLED FISH
BOBBY                          BY THE BEACH                   28-APR-11 PEPPER CRAB
CHALIE                         BY THE BEACH                   29-APR-11 GRILLED FISH
CHALIE                         BY THE BEACH                   29-APR-11 GRILLED FISH
DAHLIA                         FANTASTIC CRAB                 29-APR-11 CHILI CRAB
DAHLIA                         FANTASTIC CRAB                 29-APR-11 CHILI CRAB
DAHLIA                         FANTASTIC CRAB                 29-APR-11 PEPPER CRAB
DONNY                          BY THE BEACH                   29-APR-11 CHILI CRAB
DONNY                          BY THE BEACH                   29-APR-11 PEPPER CRAB
JAMES                          BY THE BEACH                   19-APR-11 BAKED SALMON
JAMES                          BY THE BEACH                   19-APR-11 BUTTER LOBSTER
JAMES                          BY THE BEACH                   19-APR-11 CHILI CRAB
JAMES                          BY THE BEACH                   19-APR-11 GRILLED FISH
JAMES                          FANTASTIC CRAB                 08-MAY-11 CHILI CRAB
JAMES                          FANTASTIC CRAB                 08-MAY-11 PEPPER CRAB
JAMES                          LONGEST BEACH                  29-APR-11 BUTTER LOBSTER
JAMES                          LONGEST BEACH                  29-APR-11 CHILI CRAB
JAMES                          LONGEST BEACH                  29-APR-11 GRILLED FISH
JANE                           LONGEST BEACH                  28-APR-11 PEPPER CRAB
KATE                           ALL THE BEST                   01-MAY-11 CHICKEN CHOP
KATE                           ALL THE BEST                   01-MAY-11 HERBAL CHICKEN CHOP
KATE                           ALL THE BEST                   01-MAY-11 PORK CHOP
LUKE                           ALL THE BEST                   19-APR-11 HERBAL CHICKEN CHOP
LUKE                           ALL THE BEST                   19-APR-11 LAMB CHOP
PETER                          LONGEST BEACH                  28-APR-11 BAKED SALMON
PETER                          LONGEST BEACH                  28-APR-11 PEPPER CRAB
ROBERT                         BEST FOOD                      01-MAY-11 CHICKEN CHOP
ROBERT                         BEST FOOD                      01-MAY-11 GRILLED FISH
ROBERT                         BEST FOOD                      01-MAY-11 PORK CHOP
ROBERT                         BY THE BEACH                   28-APR-11 GRILLED FISH
ROBERT                         BY THE BEACH                   29-APR-11 BAKED SALMON

38 rows selected.

The output should be Charlie and Dahlia.

SELECT DISTINCT DINER
FROM FOODORDERS
WHERE FOOD IN
(SELECT FOOD
FROM FOODORDERS
GROUP BY FOOD
HAVING COUNT(FOOD)>=2
)
AND ODATE IN
(SELECT ODATE
FROM FOODORDERS
GROUP BY ODATE
HAVING COUNT(ODATE)>=2
)
AND RESTAURANT IN
(SELECT RESTAURANT
FROM FOODORDERS
GROUP BY RESTAURANT
HAVING COUNT(RESTAURANT)>=2
);

This is what I tried, but apparently it's wrong.
Am I in the right track or what?
How should I go about doing this query?
I'm stuck for quite awhile. Help is appreciated!

Thanks in advance!

[hfx642]

Your Making this too difficult. Keep it simple.

Select Diner, Restaurant, ODate, Food, Count (*)
From FoodOrders
Group by Diner, Restaurant, ODate, Food
Having Count (*) > 1;

[pratik_garg]

hi hfx642,

as we desired same food on the same day in the same restaurant ..

think of this output this query will give group of (Diner, Restaurant, ODate, Food )combination... means only one food can be there in this output for perticular diner,restaurent,date combination then your above suggested query will not answer what andylbh is looking for...sorry but even this will give any of rows as output, beccause all four columns you have added in group by clouse..

just some modification can solve this ---

SELECT Diner, Restaurant, ODate ,food, COUNT (*) 
FROM (select FoodOrders.*,rownum from FoodOrders)
GROUP BY Diner, Restaurant, ODate ,food
HAVING COUNT (*) > 1;

or as andylbh wants distinct diner list only... then

SELECT distinct Diner 
FROM (select FoodOrders.*,rownum from FoodOrders)
GROUP BY Diner, Restaurant, ODate ,food
HAVING COUNT (*) > 1;

[pratik_garg]

i think one solution is put self join on foodorders..

in second foodorders table extract data only for this date...

and join with outer join...

one more thing put distinct Diner, Restaurant, ODate in first query else it would give multiple records of same name and restaurent

enjoy coding :)

[andylbh]

It appears that my second question turned into a new thread by the admin.

Thanks alot for this!