Re: Need help with SQL SELECT statement

From: Johannes Wahl <johannes.wahl_at_gnc.at>
Date: Mon, 19 Feb 2001 16:12:22 +0000
Message-ID: <3A9145E6.13610D36@gnc.at>

hi alessandro,
you have to use outer joins. assuming your table including the primary keys is "Table0":

SELECT

    Table0.Id_Date,
    Table1.Description,
    Table2.Description,
    Table3.Description

FROM

    Table0,
    Table1,
    Table2,

    Table3
WHERE

    Table0.Id_Table1 = Table1.Id (+) AND
    Table0.Id_Table2 = Table2.Id (+) AND
    Table0.Id_Table3 = Table3.Id (+)

ORDER BY
    Id_Date

key colums with 0 return NULL.

Alessandro Nazzani schrieb:

> Hi all,
>
> This should be simple, but I'm not an expert, so...
>
> I have a table containing 3 columns that are primary keys in 3 different
> tables. Those key columns can have values or can be set to 0.
>
> DATE ID_TABLE1 ID_TABLE2 ID_TABLE3
> 1-FEB-01 11 0 0
> 2-FEB-01 12 21 0
> 3-FEB-01 13 22 31
> 4-FEB-01 0 0 32
>
> Table 1
> ID_TABLE1 DESCR_TABLE1
> 11 Description11
> 12 Description12
> 13 Description13
>
> Table 2
> ID_TABLE2 DESCR_TABLE2
> 21 Description21
> 22 Description22
>
> Table 3
> ID_TABLE3 DESCR_TABLE3
> 31 Description31
> 32 Description32
>
> What I need is a query to retrieve all records in this table and the
> description column in the linked table (when the ID column is <> 0):
>
> DATE DESCR_TABLE1 DESCR_TABLE2 DESCR_TABLE3
> 1-FEB-01 "Description11" " " " "
> 2-FEB-01 "Description12" "Description21" " "
> 3-FEB-01 "Description13" "Description22" "Description31"
> 4-FEB-01 " " " " "Description32"
>
> How can I do that?
>
> TIA for your support and patience.
>
> Alessandro Nazzani
>
> --
> Posted from IDENT:root_at_net134-146.mclink.it [195.110.134.146]
> via Mailgate.ORG Server - http://www.Mailgate.ORG
  Received on Mon Feb 19 2001 - 10:12:22 CST