Re: SQL question

"Alex" <alexsm_at_eudoramail.com> wrote in message news:29ff93bc.0311041325.bcc6111_at_posting.google.com...
> Hi ALL
>
> I've two tables and am trying to apply an OUTER JOIN on multiple fields.
>
> Table A has some rows as:
>
> <Table A>
> --- ---
> 1 a
> 1 b
> 2 b
> 3 a
> 3 c
>
> Table B is missing some of the rows.
>
> <Table B>
> --- ---
> 1 a
> 2 b
> 3 c
>
>
> Looking for only those rows Table B is missing:
>
> ----------
> 1 b NULL
> 3 a NULL
>
>
> The following is not working:
>
> select t1.number, t1.letter, t2.number, t2.letter
> from TableA t1, TableB t2
> where t1.number = (+)t2.number
> and t1.letter = (+)t2.letter

Daft column names don't help, but this is standard behaviour: an outer join shows all the rows matched with one table AND the rows that are unique to it. So if you want to see only the rows that aren't matched off, you have to get rid of the ones that are matched.

So in new 9i language, this will work (with apologies for picking equally daft column names, but at least mine aren't reserved keywords):

select * from A left outer join B on
(A.col1 = B.col1 and A.col2 = B.col2)
minus
select * from A join B on
(A.col1 = B.col1 and A.col2 = B.col2);

Using the older-style syntax you have, you might try this:

select * from A, B
where (A.col1=B.col1(+) and A.col2=B.col2(+)) minus
select * from A,B
where (A.col1=B.col1 and A.col2=B.col2)

Works for me, at any rate, but the execution plan isn't pretty.

Regards
HJR Received on Tue Nov 04 2003 - 16:24:11 CST