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

Home -> Community -> Usenet -> c.d.o.misc -> Oracle 8.1.7.3 - SQL Syntax for LEFT JOIN on two fields

Oracle 8.1.7.3 - SQL Syntax for LEFT JOIN on two fields

From: Michael Cole <michael.cole_at_hansen.com>
Date: Thu, 18 Dec 2003 09:49:57 +1100
Message-ID: <brqmio$6fu47$1@ID-156864.news.uni-berlin.de>


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 Cole
Received on Wed Dec 17 2003 - 16:49:57 CST

Original text of this message

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