Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Oracle 8.1.7.3 - SQL Syntax for LEFT JOIN on two fields
Situation is as follows: -
I have two tables, tblMain and tblSub. I need to return various fields from tblMain, and a single field from tblSub where tblSub.fldKey = tblMain.fldSubKey AND tblSub.fldCategory = X. Note that the record may of may not exist in tblSub, so that returning a Null is acceptable.
Now I cannot use the standard LEFT JOIN syntax, because with two fields, it acts like an INNER JOIN, thus not returning a record: -
SELECT tblMain.*, tblSub.fldReturn
FROM tblMain, tblSub
WHERE tblMain.fldSubKey(+) = tblSub.fldKey AND tblSub.fldCategory = X
So I then tried a subquery: -
SELECT tblMain.*, (SELECT tblSub.fldReturn FROM tblSub WHERE
tblMain.fldSubKey = tblSub.fldKey AND tblSub.fldCategory = X)
FROM tblMain
and whilst this worked perfectly well in Oracle 9, and gave the correct result, it gave a compilation error of, "PLS-00103: Encountered the symbol "SELECT" when expecting one of..."
There possibly is an easy solution, but I have been playing with this for a couple of days now, and am having a mental blank. Any thoughts, suggestions or hints would be gratefully accepted.
-- Regards, Michael ColeReceived on Wed Dec 17 2003 - 16:49:57 CST
![]() |
![]() |