>>I was composing a SQL query in FoxPro 2.5 DOS, and ran into the need for a Left-Outer-Join. Is there a way to create such a query using the SQL-Select command?
>>
>>Thanks, Chris
>
>Chris,
>
>Yes there is, it involves a UNION and a subSELECT. Psuedo code follows;
>
>
>SELECT Table1.Field1, Table2.Field2 ;
> FROM Table1, Table2 ;
> WHERE Table1.Id = Table2.Id ;
> UNION SELECT Table1.Fiedl1, SPACE(10) ;
> FROM Table1 ;
> WHERE Table1.Id NOT IN(SELECT Id FROM Table2) ;
> INTO CURSOR MyLOJ
>
>
>The SPACE(10) is aplace holder for the fiedl from table2. It must be the same size and data type as the field in table2.
Don't forget that UNION without the ALL modifier will remove duplicate records from the result set whereas a left outer join would not remove duplicates so your result set may have fewer records than TABLE1.