www.webdeveloper.com
Results 1 to 2 of 2

Thread: Need help with a query

  1. #1
    Join Date
    May 2011
    Posts
    1

    Need help with a query

    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.

    Code:
    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?

  2. #2
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    This worked for me.

    Code:
    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.

    Code:
    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:

    Code:
    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
    Last edited by eval(BadCode); 05-09-2011 at 03:52 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles