Carl,
All of the samples you give seem to return the right information except for:
select * ;
from foo outer join bar on ffid1 = bfid1 ;
to screen
I would expect this to return a syntax error because outer joins must be FULL, LEFT, or RIGHT as I read the docs. It seems to result in an inner join. I think what you want is a full join:
select * ;
from foo FULL outer join bar on ffid1 = bfid1 ;
to screen
>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
>
>
>*
