[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

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
0
drozeveld
Asked:
drozeveld
  • 5
  • 4
1 Solution
 
sdstuberCommented:
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')
0
 
sdstuberCommented:
actually, no,that won't work.  This requires ansi join syntax because you must outer join to more than one table
0
 
sdstuberCommented:
try 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 merch.po
       INNER JOIN merch.po_ln ON po_ln.po_cd = po.po_cd
       INNER JOIN inv.itm ON itm.itm_cd = po_ln.itm_cd
       LEFT OUTER JOIN service.se_part2po_ln ON se_part2po_ln.po_cd = po.po_cd
       LEFT OUTER JOIN so ON so.del_doc_num = se_part2po_ln.del_doc_num
       LEFT OUTER JOIN cust ON cust.cust_cd = so.cust_cd
 WHERE po.stat_cd = 'O'
   AND po.store_cd IN ('05',
                       '06',
                       '07',
                       '09')
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
drozeveldAuthor Commented:
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.
0
 
sdstuberCommented:
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.
0
 
sdstuberCommented:
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.
0
 
drozeveldAuthor Commented:
It worked!!

Thanks again.  You've been VERY helpful!!
0
 
drozeveldAuthor Commented:
Quick responses and very easy to understand instructions.
0
 
drozeveldAuthor Commented:
OK, I understand what you mean now.   Have a great weekend!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now