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!