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 Received on Mon Oct 02 2000 - 11:45:15 CDT