I am trying to map 3 tables in oracle database:
testHdr, testDet, testOther
SELECT hdr.*, det.*, othr.*
FROM testHdr hdr, testDet det, testOther othr
where hdr.adj_no = det.adj_no (+)
and hdr.adj_no2 = det.adj_no2 (+)
and i.key3 = det.key3
and hdr.adj_no = '112'
ORA-01416: two tables cannot be outer-joined to each other
You should use an explicit left join:
SELECT hdr.*, det.*, othr.* FROM testHdr hdr LEFT JOIN testDet det ON hdr.adj_no = det.adj_no AND hdr.adj_no2 = det.adj_no2 LEFT JOIN testOther othr -- ON <some condition> WHERE i.key3 = det.key3 AND hdr.adj_no = '112'
In your original query you never specified any join conditions for the
testOther table, which means you are doing a cross join of that table. While this should not affect your actual problem, if the cross join were not intended, you should add an
ON clause restricting this join.
One reason why most of the database world has moved away from the implicit join syntax you used is precisely because it makes it hard to see what is being joined, and it also makes it easier to miss a join condition.