You got the correct results in each case. The last one with the union was a valiant effort and somewhat like how we used to do it before FoxPro offered joins. We would union all of those that met the where clause critieria with those that didn't. In VFP it's a lot easier.
select * from table1 FULL JOIN table2 on joinexpression

FULL is actually shorthand for FULL OUTER JOIN. The OUTER is optional, FULL is not.

The result set will be all of those that met the join criteria plus those from table1 that had no match in table2 (with all table2 fields set to NULL) plus all of those in table2 that had no match in table1 (with all table1 fields set to NULL).
Is this what you're looking for?

>I am trying to join two tables (check and invoice totals) on a foreign key (job id). I don't really need anything from the parent so I am not including it in the query. however, there are checks and invoices that are not part of a Job, and there for the Job ID is null. My attempts at joining them led me to the sample below that does not seem to behave as I would expect, Can someone tell me if this is a bug, or is there a better way to solve it than the final query involving a union?
>
>Carl
>
>*

>
>create database foobar
>create table foo ( fkey i null, ffid1 c(1) null )
>insert into foo values( 1, "a" )
>insert into foo values( 2, "b" )
>insert into foo values( 3, "c" )
>
>create table bar ( bkey i null, bfid1 c(1) null )
>insert into bar values( 1, "b" )
>insert into bar values( 2, "c" )
>insert into bar values( 3, "d" )
>
>select * ;
> from foo inner join bar on ffid1 = bfid1 ;
> to screen
>
>*     FKEY FFID1    BKEY BFID1
>*        2 b           1 b
>*        3 c           2 c
>*
>
>select * ;
> from foo left join bar on ffid1 = bfid1 ;
> to screen
>*
>*     FKEY FFID1    BKEY BFID1
>*        1 a      .NULL. .NULL.
>*        2 b           1 b
>*        3 c           2 c
>*
>
>select * ;
> from foo right join bar on ffid1 = bfid1 ;
> to screen
>
>*
>*     FKEY FFID1    BKEY BFID1
>*        2 b           1 b
>*        3 c           2 c
>*   ,NULL. .NULL.      3 d
>*
>
>select * ;
> from foo outer join bar on ffid1 = bfid1 ;
> to screen
>
>*
>*     FKEY FFID1    BKEY BFID1
>*        2 b           1 b
>*        3 c           2 c
>*
>select * ;
> from foo left join bar on ffid1 = bfid1 ;
>union select * ;
>  from foo right join bar on ffid1 = bfid1 ;
>  to screen
>
>*
>*     FKEY FFID1    BKEY BFID1
>*   .NULL. .NULL.      3 d
>*        1 a      .NULL. .NULL.
>*        2 b           1 b
>*        3 c           2 c
>
>
>*