I have a query as follow,
SELECT payment_txn_status, COUNT(*) AS "count", YEAR(tb.date_created) AS date_created
FROM tpayment_txn tt
JOIN tbooking tb
ON tb.booking_ref_id = tt.booking_ref_id
GROUP BY tt.payment_txn_status, YEAR(tb.date_created)
which gives me result like ,
+
|payment_txn_status |count | date_created |
+
| 210 | 68 | 2011 |
| 201 | 34 | 2012 |
| 205 | 57 | 2011 |
(8 rows affected)
I have one more table named
payment_txn_status_ref
which contains description for each payment staus code as follow,
+
|payment_status |description|
+
|201 | Created |
|202 | Progress |
|202 | Collected |
|205 | Paid |
So I want to modify above query so that it will replace payment_txn_status code with their respective description from payment_txn_status_ref table
The result should be look like ,
+
|payment_txn_status |count | date_created |
+
| Created | 68 | 2011 |
| Progress | 34 | 2012 |
| Paid | 57 | 2011 |
and so on....