Select query question

[andylbh]

Thanks so much guys! That solved my problem..
I have another question..

Find the names of all restaurants together with the names of diners who visit the restaurants on 24 April 2011. IF no diner visited a restaurant on the day, include a name of restaurant with no value in column DINER. Order the output by the name of the restaurant.

SQL> select * from foodorders;

DINER                          RESTAURANT                     ODATE     FOOD
------------------------------ ------------------------------ --------- ------------------------------
JAMES                          LONGEST BEACH                  29-APR-11 GRILLED FISH
JAMES                          LONGEST BEACH                  29-APR-11 BUTTER LOBSTER
JAMES                          LONGEST BEACH                  29-APR-11 CHILI CRAB
JAMES                          BY THE BEACH                   19-APR-11 GRILLED FISH
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
ROBERT                         BEST FOOD                      01-MAY-11 GRILLED FISH
ROBERT                         BEST FOOD                      01-MAY-11 CHICKEN CHOP
ROBERT                         BEST FOOD                      01-MAY-11 PORK CHOP
ALICE                          LONGEST BEACH                  29-APR-11 BAKED SALMON

DINER                          RESTAURANT                     ODATE     FOOD
------------------------------ ------------------------------ --------- ------------------------------
ALICE                          LONGEST BEACH                  29-APR-11 PEPPER CRAB
ROBERT                         BY THE BEACH                   29-APR-11 BAKED SALMON
BOB                            BY THE BEACH                   29-APR-11 BAKED SALMON
BOB                            BY THE BEACH                   29-APR-11 CHILI CRAB
CHALIE                         BY THE BEACH                   29-APR-11 GRILLED FISH
CHALIE                         BY THE BEACH                   29-APR-11 GRILLED FISH
DONNY                          BY THE BEACH                   29-APR-11 PEPPER CRAB
DONNY                          BY THE BEACH                   29-APR-11 CHILI CRAB
PETER                          LONGEST BEACH                  28-APR-11 BAKED SALMON
PETER                          LONGEST BEACH                  28-APR-11 PEPPER CRAB
JANE                           LONGEST BEACH                  28-APR-11 PEPPER CRAB

DINER                          RESTAURANT                     ODATE     FOOD
------------------------------ ------------------------------ --------- ------------------------------
ALICE                          BEST FOOD                      09-APR-11 BAKED SALMON
ALICE                          BEST FOOD                      09-APR-11 GRILLED FISH
JAMES                          FANTASTIC CRAB                 08-MAY-11 PEPPER CRAB
JAMES                          FANTASTIC CRAB                 08-MAY-11 CHILI CRAB
LUKE                           ALL THE BEST                   19-APR-11 HERBAL CHICKEN CHOP
LUKE                           ALL THE BEST                   19-APR-11 LAMB CHOP
KATE                           ALL THE BEST                   01-MAY-11 HERBAL CHICKEN CHOP
KATE                           ALL THE BEST                   01-MAY-11 CHICKEN CHOP
KATE                           ALL THE BEST                   01-MAY-11 PORK CHOP
ROBERT                         BY THE BEACH                   28-APR-11 GRILLED FISH
BOBBY                          BY THE BEACH                   28-APR-11 GRILLED FISH

DINER                          RESTAURANT                     ODATE     FOOD
------------------------------ ------------------------------ --------- ------------------------------
BOBBY                          BY THE BEACH                   28-APR-11 PEPPER CRAB
BOBBY                          BY THE BEACH                   28-APR-11 BAKED SALMON
DAHLIA                         FANTASTIC CRAB                 29-APR-11 PEPPER CRAB
DAHLIA                         FANTASTIC CRAB                 29-APR-11 CHILI CRAB
DAHLIA                         FANTASTIC CRAB                 29-APR-11 CHILI CRAB

38 rows selected.

Above is the full table contents.
I've done the first part of the query:

SQL> SELECT RESTAURANT, DINER
  2  FROM FOODORDERS
  3  WHERE ODATE='24-APR-2011'
  4  ORDER BY RESTAURANT;

no rows selected

What does the IF part of the question there means?
How am I suppose to show something that don't exist??

Thanks in advance!

[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]

SQL> SELECT DISTINCT FO1.RESTAURANT, FO1.DINER
  2  FROM FOODORDERS FO1 LEFT OUTER JOIN FOODORDERS FO2
  3  ON FO1.ODATE = FO2.ODATE
  4  WHERE FO2.ODATE='24-APR-2011'
  5  ORDER BY RESTAURANT;

no rows selected

Am I on the right track? I'm still not getting any results.
What should be the expected results?
Is it like this: (self typed)

RESTAURANT                     DINER
------------------------------ ------------------------------
LONGEST BEACH                  NULL
ALL THE BEST                   NULL
BEST FOOD                      NULL
FANTASTIC CRAB                 NULL
BY THE BEACH                   NULL

[andylbh]

ah it's alright, managed to get it done
Thanks for the hint pratik_garg!

[pratik_garg]

well great but in above query we need to edit something - (for anyone else)

SELECT DISTINCT FO1.RESTAURANT, [B]FO2.DINER [/B]
FROM FOODORDERS FO1 
LEFT OUTER JOIN 
([B]select * from FOODORDERS   
         WHERE FO2.ODATE='24-APR-2011'[/B])FO2 
 
ON( FO1.ODATE = FO2.ODATE  [B]and FO1.RESTAURANT=FO2.RESTAURANT and FO1.DINER =FO2.DINER [/B])
ORDER BY RESTAURANT;

[hfx642]

When comparing dates to literal strings,
you should always use the To_Date function.

WHERE FO2.ODATE=To_Date ('24-APR-2011', 'DD-MON-YYYY')

Just to be sure that there is no implicit conversion.
(Someone else might have a different NLS_DATE_FORMAT.)

[pratik_garg]

@hfx642
great point ....

I forgot to add this before..
thanks for adding it.

but if you want to use it in currect way or to extract data for this date you have to use it on column not on static date.

as

to_char(ODATE,'DD-MON-YYYY')='24-APR-2011'

or else it will not extract data for date with time stamp
e.g. it will not include row having '24-apr-2011 11:22:33' as odate..

one more mistake i have done is use of alias FO2 in inner query.

please do not use it. this alias name is for output of this inner query so that we can't use this in inner query.

SELECT DISTINCT FO1.RESTAURANT, FO2.DINER 
FROM FOODORDERS FO1 
LEFT OUTER JOIN 
(select * from FOODORDERS   
         WHERE to_char(ODATE,'DD-MON-YYYY')='24-APR-2011')FO2 
 
ON( FO1.ODATE = FO2.ODATE  
    and FO1.RESTAURANT=FO2.RESTAURANT 
    and FO1.DINER =FO2.DINER )
ORDER BY RESTAURANT;

[hfx642]

I was going on the assumption that the ODate column was JUST a date and did NOT have a time component.
Also, if ODate has been indexed, the function will ignore the index on a search of THAT column.
The ONLY date column (in MY DB) that has a time component is an audit column of either Insert_Date or Update_Date (OR... If I have a column specifically for Time).
Other than that, a date column should never have a time component.
Trunc that Date, baby!!

[pratik_garg]

@ hfx642

first thing if date column is like this only in your database (without timestamp)
then this is unnecessary to use to_char() for checking.
this would be a overhead for oracle DB..

if you have confidence on the data in this table will not be having time stamp it is better don't use even trancate function in the query..

is it right .. isn't it??

[hfx642]

Yes. You are correct. However...
My point was to circumvent the implict literal string to date conversion.
MY NSL_Date_Format is different from the Oracle default of 'DD-MON-YYYY',
which may be different from the users, which may be different from yours, etc.
That is why I ALWAYS explicitly convert literal strings to dates.