Thanks all! :o)


>>All fields are character.
>>
>>Table1 fields:
>>
>>aid
>>aagcy
>>adescriptn
>>
>>Table2 fields:
>>
>>bidv (same value as aid in table1)
>>bname
>>bvalue
>>
>>I want to join the above two tables and do the following:
>>
>>1. Create a single record for every distinct or unique aid record in table1 whether or not a match exists in table2.
>>2. Include the fields: table1.aid, table1.aagcy, table2.bvalue
>>3. If there is no matching record in table2, then I want to populate the bvalue field in the joined cursor with an empty string value ''.
>>
>>I am stuck on a simple sql statement which amazes me. I just can't concentrate today :o( Maybe it was the only 2 hours of sleep I got last night? :o)
>
>Assuming aID is unique in table1
>

>select T1.*, nvl(S1.bname, space(10)) as bName, ;
>NVL(S1.bValue,space(10)) as bValue from Table1 T1 ;
>left join (select bID, max(bName) as bName, max(bValue) as bValue ;
>group by 1 from Table1) S1 on T1.aID = S1.bID

>
>If we may have duplicates in Table2 on bID. Otherwise just a left join of table1 and table2
>
>Instead of space(10) it should be number of spaces for the field length.