title: Complex Full Outer Join
body: <p>Sigh ... can anyone help? In the SQL query below, the results I get are incorrect. There are three (3) labor records in [LaborDetail]</p>
  
  <ul>
  <li><strong>Hours / Cost</strong></li>
  <li>2.75 / 50.88</li>
  <li>2.00 / 74.00</li>
  <li>1.25 / 34.69</li>
  </ul>
  
  <p>There are two (2) material records in [WorkOrderInventory]</p>
  
  <ul>
  <li><strong>Material Cost</strong></li>
  <li>42.75</li>
  <li>35.94</li>
  </ul>
  
  <p>The issue is that the query incorrectly returns the following:</p>
  
  <pre><code>sFunction      cntWO    sumLaborHours   sumLaborCost    sumMaterialCost
  ROBOT HARNESS   1         12              319.14              236.07
  </code></pre>
  
  <p>What am I doing wrong in the query that is causing the sums to be multiplied? The correct values are sumLaborHours = 6, sumLaborCost = 159.57, and sumMaterialCost = 78.69. Thank you for your help.</p>
  
  <pre><code>SELECT CASE WHEN COALESCE(work_orders.location, Work_Orders_Archived.location) IS NULL
      THEN '' ELSE COALESCE(work_orders.location, Work_Orders_Archived.location) END AS sFunction,
  (SELECT COUNT(*)
      FROM work_orders
          FULL OUTER JOIN Work_Orders_Archived
          ON work_orders.order_number = Work_Orders_Archived.order_number
      WHERE COALESCE(work_orders.order_number, Work_Orders_Archived.order_number) = '919630') AS cntWO,
  SUM(Laborhours) AS sumLaborHours,
  SUM(LaborCost) AS sumLaborCost,
  SUM(MaterialCost*MaterialQuanity) AS sumMaterialCost
  FROM work_orders
      FULL OUTER JOIN Work_Orders_Archived
      ON work_orders.order_number = Work_Orders_Archived.order_number
      LEFT OUTER JOIN
          (SELECT HoursWorked AS Laborhours, TotalDollars AS LaborCost, WorkOrderNo
              FROM LaborDetail) AS LD
              ON COALESCE(work_orders.order_number, Work_Orders_Archived.order_number) = LD.WorkOrderNo
      LEFT OUTER JOIN
          (SELECT UnitCost AS MaterialCost, Qty AS MaterialQuanity, OrderNumber
              FROM WorkOrderInventory) AS WOI
              ON COALESCE(work_orders.order_number, Work_Orders_Archived.order_number) = WOI.OrderNumber
  WHERE COALESCE(work_orders.order_number, Work_Orders_Archived.order_number) = '919630'
  GROUP BY CASE WHEN COALESCE(work_orders.location, Work_Orders_Archived.location) IS NULL
          THEN '' ELSE COALESCE(work_orders.location, Work_Orders_Archived.location) END
  ORDER BY sFunction
  </code></pre>