|Multi Table Outer Join Help [message #428279]
||Tue, 27 October 2009 13:33
Registered: August 2005
here's a tail-end sample output of the script I am trying to devise...there should be an output of 1713 records where the last column on the right(alias, D2485955) is null...there are 1419 records where alias is null and 294 records where alias is not null.
the alias column is joined by tcn in pros_alias and batchdetail
I can only get the 294 records to output because of the way tables have to be joined to get all the data necessary for the report.
9080902 Mr. Monteith Ben DNE0910016 D2485955
9080954 Ms. Nguyen Mimosa DNET0910 D1145317
9081192 Mr. Schamp Guy DNE0910016 D3062020
9081234 Mr. Sharp Ron DNET0910 D1644404
9081398 Mrs. Urell Nancy DNE091015 D1007286
9081405 Miss Vanfossen Sheri DNE091015 D1551895
9081547 Mr. Zagorski Roger DNET0910 D3118963
294 rows selected.
here's the code I've been playing with have tried everything from inline views, scalar queries, regular requires and can't seem to find a solution,the key joins are on batchno, idnumber and tcn
thanks for any help/tips.
select po.idnumber,po.salutation, po.last, po.first,po.appealcode,pa.alias
from (select p.idnumber as idnumber,p.salutation as salutation,
p.last as last, p.first as first,bp.appealcode as appealcode,
b.batchno as batchno
from pros_view p, batchdetail b, batchpay bp
and bp.bd_id=b.transnum) po,
(select a.idnumber as idnumber,a.alias as alias, bd.batchno as batchno
from pros_alias a, batchdetail bd
where a.tcn=bd.tcn and a.idnumber=bd.idnumber) pa
order by po.idnumber;
[Updated on: Tue, 27 October 2009 13:47] by Moderator
Report message to a moderator