CODEWITHSUNDEEP
sqlunion
leinho
leinho

Reputation: 59

SQL Query replace UNION ALL with JOINS or something

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,

Upvotes: 3

Views: 22063

Answers (1)

mellamokb
mellamokb

Reputation: 56769

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.

Upvotes: 6

Related Questions