Error in SQL (Outer Join) [message #259548] |
Wed, 15 August 2007 23:31 |
mbaran
Messages: 1 Registered: August 2007
|
Junior Member |
|
|
There are four tables, Table1, Table2, Table3, Table4.
Table1 has two columns Table2_code and Table3_code where Table2_code and Table3_code are unique in Table2 and Table3 respectively.
Table4 has foreign key of tables Table2 and Table3. Combination of Table2 and Table3 returns unique row of Table4.
I want to prepare a query that return all rows from Table1, also containing Primary key of Table4 table.
I have created following query :
SELECT
Table1.Table1_Primary_Key,
Table1.Table2_code,
Table2.Table2_code,
Table1.Table3_code,
Table3.Table3_code,
Table4.Table2_Primary_Key, (primary key of Table2 in Table4 as foreign key)
Table2.Table2_Primary_Key
Table4.Table3_Primary_Key, (primary key of Table3 in Table4 as foreign key)
Table3.Table3_Primary_Key,
Table4.Table4_Primary_Key
FROM
Table1, Table4, Table2, Table3
WHERE
Table1.Table2_code = Table2.Table2_code(+)
AND Table2.Table2_Primary_Key = Table4.Table2_Primary_Key(+)
AND Table1.Table3_code = Table3.Table3_code(+)
AND Table3.Table3_Primary_Key = Table4.Table3_Primary_Key(+)
(AND Other required Conditions)
When I executing this query, I am getting an error "ORA-01417: A table may be outer joined to at most one other table".
I got the reason but not able to resolve it because of requirement i.e. all row from Table1.
Please help me.
|
|
|
|
|