Combining two queries without omitting rows

Question

I have two tables and I need records from both these tables.

Query 1

SELECT SUM(rec_issued) AS issed,
       regen_id,
       YEAR(issue_date) AS iYear,
       MONTH(issue_date) AS iMonth
FROM `view_rec_issued`
WHERE `regen_id` = 2
GROUP BY YEAR(issue_date) DESC, MONTH(issue_date) DESC
ORDER BY issue_date ASC

gives this result:

| issed | regen_id | iYear | iMonth |
-------------------------------------
|   424 |        2 |  2011 |      3 |
|  4340 |        2 |  2011 |      4 |
|  4235 |        2 |  2011 |      5 |
| 10570 |        2 |  2012 |      2 |
|  4761 |        2 |  2012 |      3 |
|  5000 |        2 |  2012 |      4 |
|  3700 |        2 |  2012 |      5 |
|  3414 |        2 |  2012 |      6 |
|  3700 |        2 |  2012 |      7 |
|  2992 |        2 |  2012 |      8 |
|   995 |        2 |  2012 |     10 |

Query 2

SELECT SUM(total_redem) AS redemed,
       regen_id,
       YEAR(redemption_date) AS rYear,
       MONTH(redemption_date) AS rMonth
FROM `recredem_month_wise`
WHERE `regen_id` = 2
GROUP BY YEAR(redemption_date) DESC, MONTH(redemption_date) DESC
order by redemption_date ASC

gives this result:

| redemed | regen_id | rYear | rMonth |
---------------------------------------
|     424 |        2 |  2011 |      3 |
|     260 |        2 |  2011 |      4 |
|    6523 |        2 |  2011 |      5 |
|    1070 |        2 |  2011 |      6 |
|     200 |        2 |  2011 |     10 |
|     500 |        2 |  2011 |     11 |
|    9750 |        2 |  2012 |      2 |
|    5000 |        2 |  2012 |      3 |
|    5500 |        2 |  2012 |      4 |
|    3803 |        2 |  2012 |      5 |
|    3700 |        2 |  2012 |      7 |
|    3000 |        2 |  2012 |      8 |

Desired result

But I want it like this:

|  issed | regen_id |  iYear | iMonth | redemed | regen_id |  rYear | rMonth |
-------------------------------------------------------------------------------
|    424 |        2 |   2011 |      3 |     424 |        2 |   2011 |      3 |
|   4340 |        2 |   2011 |      4 |     260 |        2 |   2011 |      4 |
|   4235 |        2 |   2011 |      5 |    6523 |        2 |   2011 |      5 |
|   NULL |     NULL |   NULL |   NULL |    1070 |        2 |   2011 |      6 |
|   NULL |     NULL |   NULL |   NULL |     200 |        2 |   2011 |     10 |
|   NULL |     NULL |   NULL |   NULL |     500 |        2 |   2011 |     11 |
|  10570 |        2 |   2012 |      2 |    9750 |        2 |   2012 |      2 |
|   4761 |        2 |   2012 |      3 |    5000 |        2 |   2012 |      3 |
|   5000 |        2 |   2012 |      4 |    5500 |        2 |   2012 |      4 |
|   3700 |        2 |   2012 |      5 |    3803 |        2 |   2012 |      5 |
|   3414 |        2 |   2012 |      6 |    NULL |     NULL |   NULL |   NULL |
|   3700 |        2 |   2012 |      7 |    3700 |        2 |   2012 |      7 |
|   2992 |        2 |   2012 |      8 |    3000 |        2 |   2012 |      8 |
|    995 |        2 |   2012 |     10 |    NULL |     NULL |   NULL |   NULL |

In these table regen_id is unique and I need data as YEAR and MONTH. If one table does not have any records in perticular month and year, then it should retrieve zero or NULL.

But in every record year and month should equal like this -

 iYear = rYear and iMonth = rMonth

So we can merge both the fields - No need to show year and month twice

iYear and rYear = year
iMonth and rMonth = month

---

Update

I tried with this query, almost solved the problem but records are not in sorted form "YEAR DESC, MONTH DESC".

SELECT DISTINCT A.issed, A.regen_id, A.iYear AS yrs, A.iMonth AS mnt, B.redemed, B.regen_id, B.rYear AS yrs, B.rMonth AS mnt
FROM(SELECT SUM(rec_issued) AS issed, regen_id, YEAR(issue_date) AS iYear, MONTH(issue_date) AS iMonth  
FROM `view_rec_issued` 
WHERE `regen_id` = 2 
GROUP BY YEAR(issue_date) DESC, MONTH(issue_date) DESC 
ORDER BY issue_date ASC) AS A 
LEFT JOIN 
(SELECT SUM(total_redem) AS redemed, regen_id, YEAR(redemption_date) AS rYear, MONTH(redemption_date) AS rMonth
FROM `recredem_month_wise` 
WHERE `regen_id` = 2 
GROUP BY YEAR(redemption_date) DESC, MONTH(redemption_date) DESC 
ORDER BY redemption_date ASC) AS B ON A.iYear = B.rYear AND A.iMonth = B.rMonth

UNION

SELECT DISTINCT A.issed, A.regen_id, A.iYear AS yrs, A.iMonth AS mnt, B.redemed, B.regen_id, B.rYear AS yrs, B.rMonth AS mnt
FROM(SELECT SUM(rec_issued) AS issed, regen_id, YEAR(issue_date) AS iYear, MONTH(issue_date) AS iMonth  
FROM `view_rec_issued` 
WHERE `regen_id` = 2 
GROUP BY YEAR(issue_date) DESC, MONTH(issue_date) DESC 
ORDER BY issue_date ASC) AS A 
RIGHT JOIN 
(SELECT SUM(total_redem) AS redemed, regen_id, YEAR(redemption_date) AS rYear, MONTH(redemption_date) AS rMonth 
FROM `recredem_month_wise` 
WHERE `regen_id` = 2 
GROUP BY YEAR(redemption_date) DESC, MONTH(redemption_date) DESC 
ORDER BY redemption_date ASC) AS B ON A.iYear = B.rYear AND A.iMonth = B.rMonth

Answer 1

What you describe is called a FULL OUTER JOIN: you want all rows from both tables, with the other table filled in with NULL if no match was found. Unfortunately, MySQL does not provide a full outer join, so you'll have to simulate them.

See also Full Outer Join in MySQL.

Emulate full outer join the canonical way

One way to write this would be the following, which generates NULL for missing matches.

(SELECT I.iYear AS `year`, I.iMonth AS `month`, I.issed, R.redeemed
 FROM (
  SELECT SUM(rec_issued) AS issed,
         regen_id,
         YEAR(issue_date) AS iYear,
         MONTH(issue_date) AS iMonth
  FROM `view_rec_issued`
  WHERE `regen_id` = 2
  GROUP BY YEAR(issue_date), MONTH(issue_date)
 ) I
 LEFT JOIN (
  SELECT SUM(total_redem) AS redemed,
         regen_id,
         YEAR(redemption_date) AS rYear,
         MONTH(redemption_date) AS rMonth
  FROM `recredem_month_wise`
  WHERE `regen_id` = 2
  GROUP BY YEAR(redemption_date), MONTH(redemption_date)
 ) R
 ON (I.iYear = R.rYear AND I.iMonth = R.rMonth)
)
UNION DISTINCT
(SELECT R.iYear AS `year`, R.iMonth AS `month`, I.issed, R.redeemed
 FROM (
  SELECT SUM(total_redem) AS redemed,
         regen_id,
         YEAR(redemption_date) AS rYear,
         MONTH(redemption_date) AS rMonth
  FROM `recredem_month_wise`
  WHERE `regen_id` = 2
  GROUP BY YEAR(redemption_date), MONTH(redemption_date)
 ) R
 LEFT JOIN (
  SELECT SUM(rec_issued) AS issed,
         regen_id,
         YEAR(issue_date) AS iYear,
         MONTH(issue_date) AS iMonth
  FROM `view_rec_issued`
  WHERE `regen_id` = 2
  GROUP BY YEAR(issue_date), MONTH(issue_date)
 ) I
 ON (I.iYear = R.rYear AND I.iMonth = R.rMonth)
)
ORDER BY `year` ASC, `month` ASC

You might make this shorter by writing views for the subqueries this contains, but if you want to be able to change regen_id, then this will still be rather bulky code.

Union with zeros

You can make things a lot easier by avoiding the outer joins altogether, and using a distinct union instead, filling zeros into the rows like this:

SELECT YEAR(`date`) AS `year`, MONTH(`date`) AS `month`,
       SUM(rec_issued) AS issued, SUM(total_redem) AS redeemed
FROM (
    SELECT issue_date AS `date`, rec_issued, 0 AS total_redem
    FROM view_rec_issued WHERE regen_id = 2
  UNION ALL
    SELECT redemption_date AS `date`, 0 AS rec_issued, total_redem
    FROM redemption_month_wise WHERE regen_id = 2
  ) AS U
GROUP BY EXTRACT(YEAR_MONTH FROM `date`) ASC

Left join everything to subquery finding months

The following table does the outer join simulation only to find dates occurring in either table. It then does left joins to compute the sums. As a result, it will return zero instead of NULL for missing values.

SELECT LEFT(DI.ym, 4) + 0 AS `year`, RIGHT(DI.ym, 2) + 0 AS `month`,
       DI.issed, SUM(R.total_redem) AS redeemed
FROM
 (SELECT D.ym, SUM(I.rec_issued) AS issed
  FROM
   (SELECT DISTINCT EXTRACT(YEAR_MONTH FROM issue_date) as ym
    FROM view_rec_issued
    WHERE regen_id = 2
   UNION DISTINCT
    SELECT DISTINCT EXTRACT(YEAR_MONTH FROM redemtion_date) AS ym
    FROM recredem_month_wise
    WHERE regen_id = 2
   ) AS D
  LEFT JOIN view_rec_issued AS I
         ON I.regen_id = 2
        AND EXTRACT(YEAR_MONTH FROM I.issue_date) = D.ym
 ) AS DI
LEFT JOIN recredem_month_wise AS R
       ON R.regen_id = 2
      AND EXTRACT(YEAR_MONTH FROM R.redemtion_date) = DI.ym
GROUP BY DI.ym ASC

This will have to look twice at every table one more time than the above query, so it might perhaps be slower. If you worry about performance, simply try both and benchmark them. In terms of code length, this query here is a lot more compact, and I believe more readable as well.

Note that the above query was different in a past revision of this post. That version omitted the subquery DI. The result was much nicer to read, but unfortunately wrong: if there were two issues in a month, and three redemptions, then the join would combine this to six rows for that month, thus counting every issue thrice and each redemption twice.

All queries are untested so far. If your question had come with some sample data on http://sqlfiddle.com/, then I'd have tried my queries there before posting them here.

Answer 2

Try this with order by:

SELECT * FROM 
(SELECT DISTINCT A.issed, A.regen_id, A.iYear, A.iMonth, B.redemed, B.regen_id, B.rYear, B.rMonth
FROM (SELECT SUM(rec_issued) AS issed, regen_id, YEAR(issue_date) AS iYear, MONTH(issue_date) AS iMonth  
FROM `view_rec_issued` 
WHERE `regen_id` = 2 
GROUP BY YEAR(issue_date) DESC, MONTH(issue_date) DESC 
ORDER BY issue_date ASC) AS A 
LEFT JOIN 
(SELECT SUM(total_redem) AS redemed, regen_id, YEAR(redemption_date) AS rYear, MONTH(redemption_date) AS rMonth 
FROM `recredem_month_wise` 
WHERE `regen_id` = 2 
GROUP BY YEAR(redemption_date) DESC, MONTH(redemption_date) DESC 
ORDER BY redemption_date ASC) AS B ON A.iYear = B.rYear AND A.iMonth = B.rMonth
UNION 
SELECT DISTINCT A.issed, A.regen_id, A.iYear, A.iMonth, B.redemed, B.regen_id, B.rYear, B.rMonth
FROM (SELECT SUM(rec_issued) AS issed, regen_id, YEAR(issue_date) AS iYear, MONTH(issue_date) AS iMonth  
FROM `view_rec_issued` 
WHERE `regen_id` = 2 
GROUP BY YEAR(issue_date) DESC, MONTH(issue_date) DESC 
ORDER BY issue_date ASC) AS A 
RIGHT JOIN 
(SELECT SUM(total_redem) AS redemed, regen_id, YEAR(redemption_date) AS rYear, MONTH(redemption_date) AS rMonth 
FROM `recredem_month_wise` 
WHERE `regen_id` = 2 
GROUP BY YEAR(redemption_date) DESC, MONTH(redemption_date) DESC 
ORDER BY redemption_date ASC) AS B ON A.iYear = B.rYear AND A.iMonth = B.rMonth) AS C
ORDER BY iYear DESC, iMonth DESC