Re: Problem: invite SQL...

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 09 Jul 1998 14:52:39 GMT
Message-ID: <35a5d839.7002048@192.86.155.100>

A copy of this was sent to "Dmitry M.Ivlev" <Dimon_at_Diogen.nstu.nsk.su> (if that email address didn't require changing) On Thu, 9 Jul 1998 15:09:06 +0600, you wrote:

>Hi!
>
>I have not much experience to use SQL. I want invite SQL described below
>under Oracle and can't imagine how. I have two tables (foods and what person
>ate):
>CREATE Food(FoodID INTEGER, FoodName CHAR(20))
>INSERT INTO T1 VALUES (1, 'Fish')
>INSERT INTO T1 VALUES (2, 'Milk')
>INSERT INTO T1 VALUES (3, 'Pie')
>INSERT INTO T1 VALUES (4, 'Meat')
>
>CREATE Ate(FoodID INTEGER, PersonName CHAR(20))
>INSERT INTO T2 VALUES (1, 'Mishel')
>INSERT INTO T2 VALUES (2, 'Mishel')
>INSERT INTO T2 VALUES (3, 'Mishel')
>INSERT INTO T2 VALUES (1, 'Dmitry')
>INSERT INTO T2 VALUES (4, 'Dmitry')
>
>Now how can I select (for given person) all foods plus one boolean field
>which has true if persone ate this kind of food:
>RESULT for Dmitry must be:
>1 Fish TRUE
>2 Milk FALSE
>3 Pie FALSE
>3 Meat TRUE
>
>for Mishel it gives:
>1 Fish TRUE
>2 Milk TRUE
>3 Pie TRUE
>3 Meat FALSE
>
>How can I do it making ONW QUERY?
>

it would be by using an outer join. An outer join can 'make up' missing data.

You want to join FOOD to ATE, making up rows in ATE if they don't exist. It might look like

SQL> select a.foodName, decode( b.foodId, NULL, 'FALSE', 'TRUE' )   2 from food a, ate b
  3 where a.foodId = b.foodId (+)
  4 and 'Dmitry' = b.PersonName (+)   5 /

FOODNAME DECOD

-------------------- -----
Fish                 TRUE
Milk                 FALSE
Pie                  FALSE
Meat                 TRUE

SQL> c/Dmitry/Mishel
4* and 'Mishel' = b.PersonName (+) SQL> / FOODNAME DECOD

-------------------- -----
Fish                 TRUE
Milk                 TRUE
Pie                  TRUE
Meat                 FALSE

>Dmitry.
>email: Dimon_at_Diogen.nstu.nsk.su
>
>
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jul 09 1998 - 09:52:39 CDT