Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL Query

RE: SQL Query

From: Daemen, Remco <R.Daemen_at_facent.nl>
Date: Tue, 3 Oct 2000 10:25:17 +0200
Message-Id: <10638.118366@fatcity.com>


Paul,

In your scheme there is no relation between Test and Norms, so you don't need a join condition between the tables. Try removing the condition AND NOR.NS_ID=TES.NS_ID. Check if Analyte has a column that matches Test.ns_id, and add an outer join condition on that column with Norms.ns_id.

HTH, Remco

> ----------
> From: Paul van Dijken[SMTP:paul.vandijken_at_sema.nl]
> Sent: maandag 2 oktober 2000 19:50
> To: Multiple recipients of list ORACLE-L
> Subject: SQL Query
>
> Dear All,
>
> Oracle 7.3.4
>
> Maybe I did not have enough coffe, but I can't figure this out.
> I have 4 tables Tests, Analytes, Normsystems and Norms
>
> Tests Test_id, Name, NS_Id
> Analyte A_id, Analyte, Test_Id
> Normsystems NS_ID, Name
> Norms N_Id, NS_Id, Analyte, Value
>
> +--------------------+ +--------------------+
> | Test |--------------| Normsystems |
> +--------------------+ +--------------------+
> | |
> +--------------------+ +--------------------+
> | Analyte |-------------| Norms |
> +--------------------+ +--------------------+
>
> Each Test has 1 or more Analytes
> Each Normsystems has 1 or more Norms
> Each Test has 0 or 1 Normsystem (NS_Id may be NULL)
> Each Analyte has 0 or more Norms (An analyte may be in more then 1
> normsystem)
>
> Within a specific Test I want to get all analytes together with the
> Norms.Value for each Analyte, with the value "NULL" when no norm is found
> for the Analyte.
>
> When I issue :
> select TES.TEST_ID, ANA.ANALYTE, nvl(TO_CHAR(NOR.Value),'NULL') Value
> from
> NORMS NOR, ANALYTES ANA, TEST TES
> WHERE
> TES.TEST_ID=ANA.TEST_ID
> AND NOR.NS_ID=TES.NS_ID
> AND NOR.ANALYTE(+)=ANA.ANALYTE
> AND TES.test_id=1
> ;
>
> I only receive the analytes for which a norm exists, NOT the ones for
> which
> a norm does not exist. I want both.
>
> When I try to add an extra outer join to AND NOR.NS_ID(+)=TES.NS_ID, I get
> the error
> ORA-01417: a table may be outer joined to at most one other table
>
> How do I solve this ??
>
> Paul
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Paul van Dijken
> INET: paul.vandijken_at_sema.nl
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Tue Oct 03 2000 - 03:25:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US