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: 205
        
mysql
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