asked on

Joining tables with Outer Join to display all rows, even if data is null

Here is my present sequel statement, but results are now back to only showing rows that have a DEL_DOC_NUM.

SELECT PO.PO_CD, PO.VE_CD, PO_LN.QTY_ORD, PO_LN.ITM_CD, ITM.DES, PO_LN.DEST_STORE_CD, SE_PART2PO_LN.DEL_DOC_NUM, CUST.LNAME
FROM INV.ITM ITM, MERCH.PO PO, MERCH.PO_LN PO_LN, SERVICE.SE_PART2PO_LN SE_PART2PO_LN, CUST, SO
WHERE PO_LN.PO_CD = PO.PO_CD AND PO_LN.ITM_CD = ITM.ITM_CD AND PO.PO_CD = SE_PART2PO_LN.PO_CD (+) AND SO.DEL_DOC_NUM = SERVICE.SE_PART2PO_LN.DEL_DOC_NUM AND SO.CUST_CD = CUST.CUST_CD AND ((PO.STAT_CD='O') AND (PO.STORE_CD In ('05','06','07','09')))

PO CD        VEND      Q      SKU            DEL_DOC_NUM LNAME
1211306TDRN        AFM      1      V1386      1211306TDRN        HALLENBECK
1211306TDRNA AFM      1      V7122      1211306TDRN        HALLENBECK
0109407TIOA        ASHL      1      V1261      0109407TIOA        GOODVICH
0103409THNI        BASS      1      V9528      0103409THNI        KNIAT
1231309THAM        BEST      1      V9430      1231309THAM        MOORE

Should include:

PO CD        VEND      Q      SKU            DEL_DOC_NUM LNAME
58570-040812        AAME      1      V7800
58570-04082        AAME      1      V6108
58570-0713        AAME      1      V1840

Sean Stuber

when posting sample data, please post it for EVERY table in the query, not the results of the query.

In this case though, I think I might be able to figure it out with out the data.

If you use the oracle join syntax you must put the (+) on every condition that defines the outer join.

by putting only one the join is effectively an inner join

SELECT po.po_cd,
po.ve_cd,
po_ln.qty_ord,
po_ln.itm_cd,
itm.des,
po_ln.dest_store_cd,
se_part2po_ln.del_doc_num,
cust.lname
FROM inv.itm,
merch.po,
merch.po_ln,
service.se_part2po_ln,
cust,
so
WHERE po_ln.po_cd = po.po_cd
AND po_ln.itm_cd = itm.itm_cd
AND po.po_cd = se_part2po_ln.po_cd(+)
AND so.del_doc_num = se_part2po_ln.del_doc_num(+)
AND so.cust_cd = cust.cust_cd
AND po.stat_cd = 'O'
AND po.store_cd IN ('05',
'06',
'07',
'09')

Sean Stuber

actually, no,that won't work. This requires ansi join syntax because you must outer join to more than one table

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

drozeveld

ASKER

I'm not sure I understand what "data" you are requesting, but you are correct, it didn't work because I tried adding the (+) to all the tables and it went back to excluding the PO's that don't have a DEL_DOC_NUM.

Sean Stuber

you could try the oracle outer join syntax like this

SELECT po.po_cd,
po.ve_cd,
po_ln.qty_ord,
po_ln.itm_cd,
itm.des,
po_ln.dest_store_cd,
se_part2po_ln.del_doc_num,
cust.lname
FROM inv.itm,
merch.po,
merch.po_ln,
service.se_part2po_ln,
cust,
so
WHERE po_ln.po_cd = po.po_cd
AND po_ln.itm_cd = itm.itm_cd
AND po.po_cd = se_part2po_ln.po_cd(+)
AND so.del_doc_num(+) = se_part2po_ln.del_doc_num
AND so.cust_cd = cust.cust_cd(+)
AND po.stat_cd = 'O'
AND po.store_cd IN ('05',
'06',
'07',
'09')

If these don't work, please provide a complete set of data to build test cases from
as well as expected results

I don't need the current results, if I have sample data, I can generate that myself.

Sean Stuber

By sample data I mean a few rows for each of of the tables...

itm
po
po_ln
se_part2po_ln,
cust
so

I don't have your database or your data, so I can't test anything I post.
If you give me sample data to work with it eliminates all guess work. I write the query, test it, when it gives the results you ask for I post it.

drozeveld

ASKER

It worked!!

Thanks again. You've been VERY helpful!!

drozeveld

ASKER

Quick responses and very easy to understand instructions.

drozeveld

ASKER

OK, I understand what you mean now. Have a great weekend!