How to get the last payment and the check if account is expired today or not?

Question

I want to check the user is expiring today or not on base of the last payment

My payment table is like below...

id   user_id   create_at
32   38        2014-06-05 10:30:00
33   38        2015-06-05 01:30:00

My query is as below....

I am checking who has payed 1 year ago and no more payment after

SELECT `user`.`id`,
       date_format(max(payment.create_at), ('%Y-%m-%d')) as last_payment_at,
       `payment`.`package_id`,
       `payment`.`id` as payment_id
FROM (`user`)
LEFT JOIN `payment` ON `payment`.`user_id`=`user`.`id`
WHERE date_format(payment.create_at,('%Y-%m-%d')) = '2014-06-05'
GROUP BY `payment`.`user_id` 

Instead of get zero row, I am getting the row payment_id 32.

Answer 1

You're abusing group by a little bit here. The general technique is to find the last payment in a subquery, and then join back to that query result. It is (almost) the only way to get the rest of the row details associated with the max/min value of another column, that you aren't also aggregating or grouping.

select * 
  from payments p
    inner join (
      select user_id, max(create_at) last_payment
        from payments
        group by user_id
    ) q
    on p.user_id = q.user_id
      and p.create_at = q.last_payment
    inner join user u
      on p.user_id = u.id
  where date(last_payment) = '2014-06-05'

demo here

can't be any more specific without your full table schema.