CODEWITHSUNDEEP
mysqlsqlsubquery
aanders77
aanders77

Reputation: 630

Reformat sql to show rows where main query returns null

Consider this sql:

SELECT DATE_FORMAT( Orders.Timestamp,  '%Y%m' ) AS Period,
SUM(Price) AS 'Ordersum per month and organisation', Orders.Organisation,
(
  SELECT SUM(Amount) AS Returns
  FROM Returns
  WHERE DATE_FORMAT( Returns.Timestamp,  '%Y%m' ) = Period
  AND Returns.Organisation = Orders.Organisation
) Returns
FROM Orders
GROUP BY Period, Organisation

Whenever there are rows in the subquery that doesn't have an equivalent period in the main query, the row isn't displayed. The reason is that the query takes its period from the orders table, and when the period of the subquery doesn't match a period in the orders table, it simply doesn't match the query.

Is there a way to reformat this query to achieve what I want?

Sqlfiddle here http://sqlfiddle.com/#!9/ace715/1

Upvotes: 0

Views: 39

Answers (1)

mako
mako

Reputation: 78

You can use left and right join with UNION like this:

SELECT 
ifnull(DATE_FORMAT( Orders.Timestamp,'%Y%m' ),DATE_FORMAT(Returns.Timestamp,'%Y%m' )) AS Period,
SUM(Price) AS 'Ordersum per month and organisation',
ifnull(Orders.Organisation,Returns.Organisation) as 'Organisation',
SUM(Amount) AS 'Returns'
FROM Orders
left JOIN Returns 
on DATE_FORMAT( Orders.Timestamp,'%Y%m' ) = DATE_FORMAT(Returns.Timestamp,  '%Y%m' ) 
and Returns.Organisation = Orders.Organisation
GROUP BY Period, Returns.Organisation, Orders.Organisation
union
select ifnull(DATE_FORMAT( Orders.Timestamp,  '%Y%m' ),DATE_FORMAT(Returns.Timestamp,'%Y%m' )) AS Period,
SUM(Price) AS 'Ordersum per month and organisation',
ifnull(Orders.Organisation,Returns.Organisation),
SUM(Amount) AS 'Returns'
FROM Orders
right JOIN Returns 
on DATE_FORMAT( Orders.Timestamp,  '%Y%m' ) = DATE_FORMAT(Returns.Timestamp, '%Y%m' ) 
and Returns.Organisation = Orders.Organisation
GROUP BY Period, Returns.Organisation, Orders.Organisation

Upvotes: 1

Related Questions