Need help with a query

[andylbh]

I'm suppose 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 answer is suppose to be Charlie and Dahlia,
how should I go about solving this query?

[eval(BadCode)]

This worked for me.

SELECT DINER
FROM foodorders 
GROUP BY ODATE, RESTRAUNT, DINER, FOOD 
HAVING COUNT(DINER)>1;

Result(s)-------------- 2 rows --------------------
CHALIE
DAHLIA

Edit: Alternatively if you supplied an AUTO INCREMENTAL PK you could GROUP BY everything except for the PK then do a NOT EXISTS / maxwise / NOT IN to see which records were eliminated during the grouping. Either way seems fine. It would look something like this.

SELECT foodorders.DINER 
FROM foodorders 
WHERE idfoodorders NOT IN (
  SELECT idfoodorders 
  FROM foodorders 
  GROUP BY DINER, RESTRAUNT, ODATE, FOOD
);

Result(s)-------------- 2 rows --------------------
DINER
--------
CHALIE
DAHLIA

Better yet:

SELECT foodorders.DINER
FROM foodorders 
LEFT JOIN (
  SELECT foodorders.idfoodorders
  FROM foodorders 
  GROUP BY DINER, RESTRAUNT, ODATE, FOOD
  ) as mwise
  ON mwise.idfoodorders = foodorders.idfoodorders
WHERE mwise.idfoodorders IS NULL;

 

Result(s)-------------- 2 rows --------------------
DINER
--------
CHALIE
DAHLIA

Unfortunately they were all so fast that I can't really say for sure which one is fastest... I would think the first one is fastest.

Cheers