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: 157
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