SQL - outer join with count

Question

I have 3 files

• Order Header- has order#
• Order Detail - details lines with product code and qty , keyed on order#
• Consigned Item file - list of consigned products (think products that we sell but are owned by someone else). not all products we sell are consigned; this file contains a subset of product master file. There may be duplicates in this file because key is product+supplier.

and I want to produce this resultset:

Order Number  Order Date  Number of lines     Total Qty     Total Consigned Qty
     1         20130101          5                   10                 5
     2         20130101          4                   20                 0
     3         20130101          7                   41                 20
     4         20130101          1                   66                 66

my query looks like this (simplified)

 SELECT H.OrderNo, H.Date, Count(D.LineNo), Sum(D.ProdQty)
 FROM Header H
 JOIN Detail D ON (H.PO# = D.PO#)        
 WHERE H.DATE > 20130101
 GROUP BY H.PO#, H.Date
 ORDER BY H.PO#, H.Date

Consigned Qty in the report represents the number of matches from product code on the detail file to product code on the consignment file multiplied by the quantity on the detail line.

My problem is getting consigned qty because I have a "one to maybe" relationship between "Detail File" and "Consignment File". Do I need a temporary table here, sub-select, some hand coding on each fetch?

I am using IBM/DB2 SQL, so no mysql/mssql/oracle specific tricks please.

thanks

Answer 1

If I understand the question correctly, you just need a left outer join:

 SELECT H.OrderNo, H.Date, Count(D.LineNo), Sum(D.ProdQty), count(C.ProductCode)
 FROM Header H JOIN
      Detail D
      ON H.PO# = D.PO# LEFT OUTER JOIN
      Consignment C
      on D.ProductCode = C.ProductCode       
 WHERE H.DATE > 20130101
 GROUP BY H.PO#, H.Date
 ORDER BY H.PO#, H.Date;

This brings in all the header and detail records, matching on Consignment (if possible) with NULL values when there is no match.

EDIT:

To handle multiple values in Consignment:

 SELECT H.OrderNo, H.Date, Count(D.LineNo), Sum(D.ProdQty), count(C.ProductCode)
 FROM Header H JOIN
      Detail D
      ON H.PO# = D.PO# LEFT OUTER JOIN
      (select distinct ProductCode
       from Consignment C
      ) C
      on D.ProductCode = C.ProductCode       
 WHERE H.DATE > 20130101
 GROUP BY H.PO#, H.Date
 ORDER BY H.PO#, H.Date;