Mysql query, need suggestion or solution

Posted by Xi Kam on Stack Overflow See other posts from Stack Overflow or by Xi Kam
Published on 2012-11-16T09:39:50Z Indexed on 2012/11/16 11:01 UTC
Read the original article Hit count: 133

Filed under:

Can anyone help me, i have two tables and i need records from both the table

//////////////////////////////++ 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

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

![Result from Query 1][1]

//////////////////////////////++ 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

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

![Result from Query 2][2]

But i want it as -

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

![I want this output][3]

In these table regen_id is unique and i need data as YEAR and MONTH, if in any table not have the records in perticular month and year 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

Thank You Please look at this problem.

© Stack Overflow or respective owner

Related posts about mysql