mysql - Limiting results of an sql query to last date entered per customer id -

having struggled using joins time, managed write query without syntax error. however, need limit data outputs dependant upon date.

the brief select customer's id, @ total number of loyalty points have redeemed, , last date redeemed any. query works in far collect customer id, loyalty member id, , number of points, but, every date redeemed, , not last one.

is there way can make query give last date given customer id?

my query is:

select  gv.amount gv_amount,          gv.customer_id gv_customer_id,          h.date h_date,          c.loyalty_id c_loyalty_id coupon_gv_customer gv    inner join loyalty_codes_redeem_history h          on gv.customer_id = h.customer_id     inner join customers c          on gv.customer_id = c.customers_id  

h_date in format yyyy-mm-dd hh:mm:ss

this not hard. please take @ post answered while ago background on using joins.

selecting multiple columns/fields in mysql subquery

here's trick need: subquery finds latest date each individual customer id. is:

 select customer_id, max(date) date    loyalty_codes_redeem_history   group customer_id 

then need join subquery -- virtual table -- overall query. think result this:

     select gv.amount                 gv_amount,              gv.customer_id            gv_customer_id,              customer_latest_date.date h_date,              c.loyalty_id              c_loyalty_id        coupon_gv_customer gv  inner join loyalty_codes_redeem_history h                 on gv.customer_id = h.customer_id  inner join customers c                 on gv.customer_id = c.customers_id   inner join (                  select customer_id,                          max(date) date                    loyalty_codes_redeem_history                group customer_id             ) customer_latest_date                on customer_latest_date.customer_id = c.customers_id 

do see how works? subquery used in inner join if table, in fact is: virtual table.

edit

to sum loyalty points in coupon_gv_customer table, need summary query so:

 select customer_id,         sum(amount) amount    coupon_gv_customer   group customer_id 

then, think query this. give 1 row per customer, think trying get. customers without redemptions or dates won't show up.

      select  c.customers_id,               c.loyalty_id,               customer_latest_date.date               customer_points.amount          customers c   inner join  (                     select customer_id,                            sum(amount) amount                       coupon_gv_customer                   group customer_id               ) customer_points on c.customers_id = customers_points.customer_id   inner join  (                     select customer_id,                             max(date) date                       loyalty_codes_redeem_history                   group customer_id             ) customer_latest_date on customer_latest_date.customer_id = c.customers_id