>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.