Re: Making Matched Records with Primary File in Query an Outer join in SQL

[Pete Helgren <Pete@xxxxxxxxxx>]

Thanks Rob.  The example data in this case was much simpler and easier 
to follow than the real data I was attempting to unravel.  Very helpful.

Can I assume then that Query/400 is using "left outer join" when using 
the "Matched Records with Primary File" option? (as you have presented 
here) Or, is that an invalid assumption?

Pete


rob@xxxxxxxxx wrote:
> I think that is one of those cases where ANZQMQRY is going to throw you 
> some warnings that RTVQMQRY is not going to tell you exactly what you saw 
> in Query/400.
>
> select * from pete1
> 1....+....2....+....
> KEY1           DATA1
>    1               1
>    1               2
>    1               3
>    2               1
>    2               5
>  End of data  ******
>
> select * from pete2
> 1....+....2....+....
> KEY2           DATA2
>    1              10
>    2              11
>    3              12
>    4              13
>  End of data  ******
>
> select * from pete3
> 1....+....2....+....3....+....4....+
> KEY3          DATA3A          DATA3B
>    1              20              30
>    3              22              32
>    4              23              33
>    5              24              34
>  End of data  ******** 
>
> select * from pete4
> .1....+....2....+....3....+....4....+
> KEY4A           KEY4B           DATA4
>     2              41              51
>     3              42              52
>     4              43              53
>     5              44              54
>   End of data  ******** 
>
> select * from pete5
> 1....+....2....+....
> KEY5           DATA5
>    1              60
>    2              61
>    5              64
>  End of data  ******
>
> select key1, data1, 
>        key2, data2, 
>        key3, data3a, data3b, 
>        key4a, key4b, data4, 
>        key5, data5 
> from pete1 left outer join pete2 on data1=key2 
>            left outer join pete3 on data1=key3 
>            left outer join pete4 on data1=key4a
>            left outer join pete5 on data1=key5 
> where key1<1000 
> order by key1 
> 1....+....2....+....3....+....4....+....5....+....6....+....7....+...
> KEY1           DATA1            KEY2           DATA2            KEY3 
>    1               1               1              10               1 
>    1               2               2              11                -
>    1               3               3              12               3 
>    2               1               1              10               1 
>    2               5                -               -              5 
>  End of data  ******** 
> .8....+....9....+...10....+...11....+...12....+...13....+...14..
>          DATA3A          DATA3B           KEY4A           KEY4B 
>              20              30                -               -
>                -               -              2              41 
>              22              32               3              42 
>              20              30                -               -
>              24              34               5              44 
> *******  End of data  ******** 
> ..+...16....+...17....+...18....+...19
> DATA4            KEY5           DATA5 
>      -              1              60 
>    51               2              61 
>    52                -               -
>      -              1              60 
>    54               5              64 
> ********  End of data  ******** 
>
>
> Rob Berendt
>