Geek Answers Handbook

MySql to get all combinations of two columns with NULL if there is no corresponding entry

I have a table that records server downtime.
I created a simplified version of this table in sqlfiddle. See here sqlfiddle
The table has every entry, for example,

Reason Month Down_Time A May 2 A May 5 B May 5 C July 15 A July 3 B June 6 A June 8 C June 2

I need to write a request to get all combinations of giving Month and Reason with NULL if there is no corresponding entry
As an example: if I need to get the system downtime in May, June and July due to reasons A, B or D .. What I expect is ...

 Reason Month DOWNTIME A May 7 A June 8 A July 3 B May 5 B June 6 B July NULL D May NULL D June NULL D July NULL

Since we do not have D records in the records for the given months, this should be NULL
This is my request:

  SELECT Reasons.reason, Months.Month,sum( a.Down_time ) AS downtime FROM tabledown a RIGHT JOIN ( SELECT 'A' AS reason UNION ALL SELECT 'B' AS reason UNION ALL SELECT 'D' AS reason ) Reasons ON a.reason = Reasons.reason RIGHT JOIN ( SELECT 'May' AS month UNION ALL SELECT 'June' AS month UNION ALL SELECT 'July' AS month ) Months ON a.Month = Months.month GROUP BY Reasons.reason,Months.month ORDER BY Reasons.reason

Why I do not get the expected result :(

+4
source share
1 answer

Your first outer join , as expected, creates:

  |  REASON |  MONTH |
 -------------------
 |  A |  May |
 |  A |  May |
 |  A |  July |
 |  A |  June |
 |  B |  May |
 |  B |  June |
 |  D |  (null) |

However, since external joins produce results if the join condition is satisfied at least once (and enters only NULL entries if the condition is never fulfilled), your second outer join does not record for (B, July) ; it also falls completely Reason = 'D' because the join condition is not fulfilled (and all three months have been met elsewhere):

  |  REASON |  MONTH |
 ------------------
 |  A |  May |
 |  A |  May |
 |  B |  May |
 |  A |  June |
 |  B |  June |
 |  A |  July |

As long as you can eliminate the loss of Reason = 'D' adding OR a.Month IS NULL to your join condition, you still won't create (B, July) , Instead, since you want to get every pair (Reason, Month) , you CROSS JOIN must materialize the Reasons table in their materialized Months table:

 SELECT Reason, Month FROM ( SELECT 'A' AS Reason UNION ALL SELECT 'B' UNION ALL SELECT 'D' ) Reasons CROSS JOIN ( SELECT 'May' AS Month UNION ALL SELECT 'June' UNION ALL SELECT 'July' ) Months 
  |  REASON |  MONTH |
 ------------------
 |  A |  May |
 |  B |  May |
 |  D |  May |
 |  A |  June |
 |  B |  June |
 |  D |  June |
 |  A |  July |
 |  B |  July |
 |  D |  July |

Take a look at sqlfiddle .

Then you just need the external connection of the result to your underlying data:

 SELECT Reason, Month, SUM(Down_time) downtime FROM ( SELECT 'A' AS Reason UNION ALL SELECT 'B' UNION ALL SELECT 'D' ) Reasons CROSS JOIN ( SELECT 'May' AS Month UNION ALL SELECT 'June' UNION ALL SELECT 'July' ) Months LEFT JOIN tabledown USING (Reason, Month) GROUP BY Reason, Month 
  |  REASON |  MONTH |  DOWNTIME |
 -----------------------------
 |  A |  July |  3 |
 |  A |  June |  8 |
 |  A |  May |  7 |
 |  B |  July |  (null) |
 |  B |  June |  6 |
 |  B |  May |  5 |
 |  D |  July |  (null) |
 |  D |  June |  (null) |
 |  D |  May |  (null) |

Take a look at sqlfiddle .

+4
source

More articles:


All Articles