OraFAQ Forum: SQL & PL/SQL » Multi Table Outer Join Help

Home » SQL & PL/SQL » SQL & PL/SQL » Multi Table Outer Join Help (Windows Server 2003, Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Multi Table Outer Join Help [message #428279]

Tue, 27 October 2009 13:33

deay
Messages: 62
Registered: August 2005

Member

Hi Everyone

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
             where p.idnumber=b.idnumber
               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
where po.batchno=&batch1
and pa.batchno=po.batchno
and pa.idnumber=po.idnumber
order by po.idnumber;

[Updated on: Tue, 27 October 2009 13:47] by Moderator

Report message to a moderator