I have a payment system, and want to display the counter of each customer payment (first, second, third ...) and if it's the fist payment, display (New). How can i do this in one query ?
SELECT c.customer_name,
p.payment_datetime,
p.amount
FROM tbl_payments p
LEFT JOIN tbl_customers c ON p.customer_id = c.customer_id
ORDER BY p.payment_datetime DESC;
+---------------+-------------------+--------+
| customer_name | payment_datetime | amount |
+---------------+-------------------+--------+
| Cus1 | 2015 | 200$ |
| Cus2 | 2016 | 300$ |
| Cus1 | 2017 | 140$ |
| Cus1 | 2018 | 500$ |
+---------------+-------------------+--------+
The output must be
+---------------+-------------------+--------+--------+--------------------------------+
| customer_name | payment_datetime | amount | status | |
+---------------+-------------------+--------+--------+--------------------------------+
| Cus1 | 2015 | 200$ | Old(1) | 1 -> First payment for "cus1" |
| Cus2 | 2016 | 300$ | New | New -> "cus2" have one payment |
| Cus1 | 2017 | 140$ | Old(2) | 2 -> Second payment for "cus1" |
| Cus1 | 2018 | 500$ | Old(3) | 3 -> Third payment for "cus1" |
+---------------+-------------------+--------+--------+--------------------------------+
And on executing this query
SELECT c.customer_name,
p.payment_datetime,
p.amount
FROM tbl_payments p
LEFT JOIN tbl_customers c ON p.customer_id = c.customer_id
WHERE p.payment_datetime > '2015' AND p.payment_datetime < '2019'
ORDER BY p.payment_datetime DESC;
Get the result :
+---------------+-------------------+--------+--------+--------------------------------+
| customer_name | payment_datetime | amount | status | |
+---------------+-------------------+--------+--------+--------------------------------+
| Cus2 | 2016 | 300$ | New | New -> "cus2" have one payment |
| Cus1 | 2017 | 140$ | Old(2) | 2 -> Second payment for "cus1" |
| Cus1 | 2018 | 500$ | Old(3) | 3 -> Third payment for "cus1" |
+---------------+-------------------+--------+--------+--------------------------------+
tbl_payments
?