Re: A newbie question on SQL...

In article <170sysD43Lzv.5J5_at_netcom.com>, 170sys_at_netcom.com (170 Systems) wrote:

> Chung Ley (chung.ley_at_amd.com) wrote:
> : Hi,
 

> : I have 3 tables: Table A, Table B, and Table C.

[snip - description of outer join problem, with join from outer table to another table]

>

[snip -explanation of following solution]  

> select a.column1, a.column2, b.column1, b.column2,
> c.full_name
> from TableA a, TableB b, TableC c
> where a.key1 = b.key1 (+)
> and a.key2 = b.key2 (+)
> and decode(b.key1, null, 'Y', b.latest_flag) = 'Y'
> and decode(b.key1, null, c.userid, b.userid) = c.userid;

I think that the final join here will not produce the required results. Will the decode not force any record from table a with no record in table b to join to _ALL_ records in table c. A closer solution may be :-

         and decode(b.key1, null, -1, b.userid) = c.userid (+)

assuming that -1 will not occur as a value in c.userid.

If b.latest_flag and/or b.userid are not null columns the solution could be simplified by using nvl instead of a decode on a key field. It is worth considering using this on the userid field even if it is a nullable field as the above solution will not produce a record if there is a record in b to match a, but not a record in c to match b, but the nvl solution would.

Hope this helps. Received on Wed Feb 22 1995 - 09:56:14 CET