Re: A newbie question on SQL...

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

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

: Table A is my master table; Table B is the detail and Table C
: is just attribute data....
 

: For each record in Table A, there may or may not be any records in
: Table B. I was trying to run a outer-join query, and if there are
: record(s) in Table B, I want to use the most recent one:
 

: My query looks like this:
 

: 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 b.latest_flag = 'Y'
: and b.userid = c.userid;
 

: Well, it didn't work like I expected.... It basically gives
: back results only when there is matching keys in TableA and
: TableB. I would like me to give me null values for b.column1,
: b.column2 and c.full_name if there is no matching records in
: TableB...

Well, first of all, your outer-joins are on the wrong side. The way I remember it is put the (+) on the side that "needs more records" (i.e. the side that might not have all the records that you would want it to). Secondly, any comparisons you do against the table with the missing records should take into account that the "fake" records that are created on behalf of the outer-joined table are completely blank (i.e. all the columns are null). Try this:

    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;