SQL Query replace UNION ALL with JOINS or something

sql, union

Solution

Sure, just `LEFT JOIN` in the tables and sum the appropriate columns:

SELECT
  O.Date,
  O.Machine,
  SUM(COALESCE(O.Planning, 0)) AS Planning,
  SUM(COALESCE(G.Pieces, 0)) AS GoodPieces,
  SUM(COALESCE(B.Pieces, 0)) AS BadPieces
FROM
  Orders O
LEFT JOIN
  (SELECT G.OrderID,
     SUM(COALESCE(G.Pieces, 0)) AS GoodPieces
   FROM GoodPieces G
   GROUP BY G.OrderID) G ON G.OrderID = O.ID
LEFT JOIN
  (SELECT B.OrderID,
     SUM(COALESCE(B.Pieces, 0)) AS BadPieces
   FROM BadPieces B
   GROUP BY B.OrderID) B ON B.OrderID = O.ID
GROUP BY
  O.Date,
  O.Machine;

Demo: http://www.sqlfiddle.com/#!3/09a73/17

Edit: Updated to handle point given by `@MikaelEriksson` in comments.

Problem

Now I have 3 tables Orders: ID | Date | Machine | Planning GoodPieces: ID | Date | Machine | Pieces | Product BadPieces: ID | Date | Machine | Pieces | Product | Component The output of the query is Date | Machine | Planning | GoodPieces | BadPieces The actual query is something like that: ``` SELECT data.Date AS Date, data.Machine AS Machine, SUM(CASE WHEN data.type = 'Planning' THEN data.value END ) AS Planning, SUM(CASE WHEN data.type = 'GoodPieces' THEN data.value END ) AS GoodPieces, SUM(CASE WHEN data.type = 'BadPieces' THEN data.value END ) AS BadPieces FROM ( SELECT 'Planning' AS Type, Date AS Date, Machine AS Machine, Planning AS Value FROM Orders UNION ALL SELECT 'GoodPieces', Date, Machine, Pieces AS Value FROM GoodPieces UNION ALL SELECT 'BadPieces' Date, Machine, Pieces AS Value FROM BadPieces ) AS data GROUP BY Date, Machine ``` My question is if is there a way to get the same output with this 3 tables Orders: ID | Date | Machine| Planning GoodPieces: OrderID | Pieces | Product BadPieces: OrderID | Pieces | Product | Component Thanks,

Original source