Re: Oracle 8.1.7.3 - SQL Syntax for LEFT JOIN on two fields

On Thu, 18 Dec 2003 09:49:57 +1100, "Michael Cole" <michael.cole_at_hansen.com> wrote:

>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.
<snip>
>
>SELECT tblMain.*, tblSub.fldReturn
> FROM tblMain, tblSub
> WHERE tblMain.fldSubKey(+) = tblSub.fldKey AND tblSub.fldCategory = X

II'm a little tired tonight, and maybe not thinking too clearly, but my first thought here is that tblSub is the optional table, at least if I understand you correctly, so you should move your "+" as follows:

SELECT tblMain.*, tblSub.fldReturn
 FROM tblMain, tblSub
WHERE tblMain.fldSubKey = tblSub.fldKey(+) AND tblSub.fldCategory(+) = X

Does this help? If not, you could try a UNION:

SELECT tblMain.*, tblSub.fldReturn
 FROM tblMain
WHERE tblMain.fldSubKey IS NULL
UNION ALL
SELECT tblMain.*, tblSub.fldReturn
 FROM tblMain, tblSub
WHERE tblMain.fldSubKey = tblSub.fldKey
AND tblSub.fldCategory = X

The first SELECT picks up the case where there are no matching tblSub rows, and the second SELECT picks up the case where there are matching rows.

Jonathan Gennick --- Brighten the corner where you are http://Gennick.com

Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to Oracle-article-request_at_gennick.com and include the word "subscribe" in either the subject or body. Received on Wed Dec 17 2003 - 21:11:33 CST