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

That's an ugly beast. Clearly you've got a cartesian product at some point, doubt it will be obvious without examining data. — Hart CO, Jun 03 '13 at 18:28

ChrisCamp · Answer 1 · 2013-06-03T18:49:27.657

1

Try using the SUM function inside a derived table subquery when doing the full join to "WorkOrderInventory" like so...

select 
...  
 sum(hrs) as sumlaborhrs, 
 sum(cost) as sumlaborcost, 
 -- calculate material cost in subquery 
 summaterialcost
from labordetail a
full outer join 
 (select ordernumber, sum(materialcost) as summaterialcost
  from WorkOrderInventory 
  group by ordernumber
 ) b on a.workorderno = b.ordernumber

i created a simple sql fiddle to demonstrate this (i simplified your query for examples sake)

edited Jun 03 '13 at 18:49

answered Jun 03 '13 at 18:43

ChrisCamp

672
1
5
20

Thanks Chris. Your suggestion worked in conjunction with hoi's above. – Charles Hamilton Jun 03 '13 at 19:44

score 0 · Answer 2 · answered Jun 03 '13 at 18:27

The best guess is that the work orders appear more than once in one of the tables. Try these queries to check for duplicates in the two most obvious candidate tables:

select cnt, COUNT(*), MIN(order_number), MAX(order_number)
from (select order_number, COUNT(*) as cnt
      from work_orders
      group by order_number
     ) t
group by cnt
order by 1;

select cnt, COUNT(*), MIN(order_number), MAX(order_number)
from (select order_number, COUNT(*) as cnt
      from work_orders_archived
      group by order_number
     ) t
group by cnt
order by 1;

If either returns a row where cnt is not 1, then you have duplicates in the tables.

Hello Gordon, First - thank you for taking the time to answer. The order_number in the two tables you identified are UNIQUE keys and do not repeat. However, in the other tables, the order_number is used as a foriegn key and do repeat. — Charles Hamilton, Jun 03 '13 at 18:37

hol · Accepted Answer · 2013-06-03T20:07:23.243

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

Thank you for the response hoi. Your assumption is correct - work_orders and Work_Orders_Archived do contain the same thing. The database is on SQL Server 2008. — Charles Hamilton, Jun 03 '13 at 18:45
Thanks hoi. Your answer helped a bunch. I also used Chris's suggestion below. — Charles Hamilton, Jun 03 '13 at 19:44

Complex Full Outer Join

3 Answers3