Mysql query, need suggestion or solution
- by Xi Kam
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.
