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

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