Complex Full Outer Join

Question

Sigh ... can anyone help? In the SQL query below, the results I get are incorrect. There are three (3) labor records in [LaborDetail]

• Hours / Cost
• 2.75 / 50.88
• 2.00 / 74.00
• 1.25 / 34.69

There are two (2) material records in [WorkOrderInventory]

• Material Cost
• 42.75
• 35.94

The issue is that the query incorrectly returns the following:

sFunction      cntWO    sumLaborHours   sumLaborCost    sumMaterialCost
ROBOT HARNESS   1         12              319.14              236.07

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.

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

Answer 1

Looks to me that work_orders and work_orders_archived contains the same thing and you need both tables as if they were one table. So you could instead of joining create a UNION and use it as if it was one table:

select location as sfunction
from
(select location
  from work_orders
union  location
  from work_orders_archived)

Then you use it to join the rest. What DBMS are you on? You could use WITH. But this does not exist on MYSQL.

with wo as 
(select location as sfunction, order_number
  from work_orders
union  location, order_number
  from work_orders_archived)
select sfunction,
count(*) 
SUM(Laborhours) AS sumLaborHours,
SUM(LaborCost) AS sumLaborCost,
SUM(MaterialCost*MaterialQuanity) AS sumMaterialCost
from wo
    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 wo.order_number = '919630'
group by sfunction
order by sfunction