I am having 2 mysql tables -
a) lessee
b) lessee_payment
Using these two tables i want to generate a month wise record of all the lessees for a specific year.
my "lessee" table is like this:
+----+-------+--------+
| id | name | amount |
+----+-------+--------+
| 1 | Amit | 5000 |
| 2 | Sumit | 6000 |
| 3 | Rahul | 4000 |
| 4 | Pooja | 7000 |
| 5 | Raja | 5000 |
+----+-------+--------+
my "lessee_payment" table is like this:
+----+-----------+------------+----------------+
| id | lessee_id | date | collected(Y/N) |
+----+-----------+------------+----------------+
| 1 | 1 | 2016-07-05 | Y |
| 2 | 2 | 2016-07-08 | Y |
| 3 | 3 | 2016-07-05 | N |
| 4 | 1 | 2016-08-05 | Y |
| 5 | 2 | 2016-08-08 | Y |
| 6 | 1 | 2016-09-05 | Y |
| 7 | 2 | 2016-09-05 | Y |
| 8 | 3 | 2016-09-08 | Y |
| 9 | 1 | 2016-10-05 | N |
| 10 | 2 | 2016-10-08 | Y |
| 11 | 3 | 2016-10-07 | Y |
| 12 | 4 | 2016-10-05 | Y |
+----+-----------+------------+----------------+
And month-wise record which i want to show in html table:
+--------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+
| Lessee | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
+--------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+
| Amit | - | - | - | - | - | - | Y | Y | Y | N | - | - |
| Sumit | - | - | - | - | - | - | Y | Y | Y | Y | - | - |
| Rahul | - | - | - | - | - | - | N | - | Y | Y | - | - |
| Pooja | - | - | - | - | - | - | - | - | - | Y | - | - |
| Raja | - | - | - | - | - | - | - | - | - | - | - | - |
+--------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+
Problem is i am unable to show the record in above tabular format.
Try this mysql query:
SELECT
a.name,
if(MAX(Jan), if(MAX(Jan) = 1, 'Y', 'N'), '-') AS Jan,
if(MAX(Feb), if(MAX(Feb) = 1, 'Y', 'N'), '-') AS Feb,
if(MAX(Mar), if(MAX(Mar) = 1, 'Y', 'N'), '-') AS Mar,
if(MAX(Apr), if(MAX(Apr) = 1, 'Y', 'N'), '-') AS Apr,
if(MAX(May), if(MAX(May) = 1, 'Y', 'N'), '-') AS May,
if(MAX(Jun), if(MAX(Jun) = 1, 'Y', 'N'), '-') AS Jun,
if(MAX(Jul), if(MAX(Jul) = 1, 'Y', 'N'), '-') AS Jul,
if(MAX(Aug), if(MAX(Aug) = 1, 'Y', 'N'), '-') AS Aug,
if(MAX(Sep), if(MAX(Sep) = 1, 'Y', 'N'), '-') AS Sep,
if(MAX(Oct), if(MAX(Oct) = 1, 'Y', 'N'), '-') AS Oct,
if(MAX(Nov), if(MAX(Nov) = 1, 'Y', 'N'), '-') AS Nov,
if(MAX(`Dec`), if(MAX(`Dec`) = 1, 'Y', 'N'), '-') AS `Dec`
FROM (SELECT
lessee.id,
lessee.name,
if(month(lessee_payment.date) = 1, if(collected = 'Y', 1, 2), 0) AS Jan,
if(month(lessee_payment.date) = 2, if(collected = 'Y', 1, 2), 0) AS Feb,
if(month(lessee_payment.date) = 3, if(collected = 'Y', 1, 2), 0) AS Mar,
if(month(lessee_payment.date) = 4, if(collected = 'Y', 1, 2), 0) AS Apr,
if(month(lessee_payment.date) = 5, if(collected = 'Y', 1, 2), 0) AS May,
if(month(lessee_payment.date) = 6, if(collected = 'Y', 1, 2), 0) AS Jun,
if(month(lessee_payment.date) = 7, if(collected = 'Y', 1, 2), 0) AS Jul,
if(month(lessee_payment.date) = 8, if(collected = 'Y', 1, 2), 0) AS Aug,
if(month(lessee_payment.date) = 9, if(collected = 'Y', 1, 2), 0) AS Sep,
if(month(lessee_payment.date) = 10, if(collected = 'Y', 1, 2), 0) AS Oct,
if(month(lessee_payment.date) = 11, if(collected = 'Y', 1, 2), 0) AS Nov,
if(month(lessee_payment.date) = 12, if(collected = 'Y', 1, 2), 0) AS `Dec`
FROM lessee
LEFT JOIN lessee_payment
ON lessee_payment.lessee_id=lessee.id
GROUP BY lessee.id, month(date)) AS a GROUP BY a.id