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

Question

Having struggled with using JOINS for some time, i finally managed to write a query without a Syntax Error. However, i need to limit the data it outputs dependant upon the date.

The brief is to select a customer's id, then look at the total number of loyalty points they have redeemed, and also get the LAST DATE they redeemed any. My query works in so far as it will collect customer id, loyalty member id, and number of points, BUT, i get every date they redeemed, and not just the last one.

Is there a way i can make the query only give the last date for a given customer id?

My query is:

SELECT  gv.amount AS gv_amount, 
        gv.customer_id AS gv_customer_id, 
        h.date AS h_date, 
        c.loyalty_id AS c_loyalty_id
FROM 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 is in the format YYYY-MM-DD HH:MM:SS

score 0 · Answer 1 · edited May 23 '17 at 10:25

This is not hard. Please take a look at this post I answered a while ago for some background on using JOINs.

Selecting multiple columns/fields in MySQL subquery

Here's the trick you need: A subquery that finds the latest date for each individual customer id. This is:

 SELECT customer_id, MAX(date) as date
   FROM loyalty_codes_redeem_history
  GROUP BY customer_id

Then you need to join this subquery -- this virtual table -- into your overall query. I think the result will look like this:

     SELECT gv.amount                 AS gv_amount, 
            gv.customer_id            AS gv_customer_id, 
            customer_latest_date.date AS h_date, 
            c.loyalty_id              AS c_loyalty_id
       FROM 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) AS date
                   FROM loyalty_codes_redeem_history
               GROUP BY customer_id
            ) customer_latest_date
               ON customer_latest_date.customer_id = c.customers_id

Do you see how that works? The subquery is used in an INNER JOIN as if it were a table, which in fact it is: a virtual table.

Edit

To sum up the loyalty points in your coupon_gv_customer table, you need another summary query like so:

 SELECT customer_id,
        SUM(amount) AS amount
   FROM coupon_gv_customer
  GROUP BY customer_id

Then, I think your query will be this. It will give you one row per customer, which I think is what you are trying to get. Customers without any redemptions or dates won't show up.

      SELECT  c.customers_id,
              c.loyalty_id,
              customer_latest_date.date
              customer_points.amount
        FROM  customers c
  INNER JOIN  (
                    SELECT customer_id,
                           SUM(amount) AS amount
                      FROM coupon_gv_customer
                  GROUP BY customer_id
              ) customer_points ON c.customers_id = customers_points.customer_id
  INNER JOIN  (
                    SELECT customer_id, 
                           MAX(date) AS date
                      FROM loyalty_codes_redeem_history
                  GROUP BY customer_id
            ) customer_latest_date ON customer_latest_date.customer_id = c.customers_id

I'll try for a third time to leave a comment! I do see how it works, and indeed the subquery does limit it to the last date entered by a customer. However, the combined query still returns all dates per given customer_id. I'll take a look and see if i can figure out what isn't right. Thanks for the pointers though, most helpful. — Steve Price, Mar 18 '13 at 14:19
oh, right. You have to summarize the loyalty points with another summary query .... you need a subquery like `SUM(something) AS points, customer_id FROM something GROUP BY customer_id`. But I cannot tell from your question where you are storing loyalty points. — O. Jones, Mar 18 '13 at 14:31
loyalty points and customer_id are stored in 'coupon_gv_customer'. Date redeemed and customer_id are in 'loyalty_code_redeem_history' and loyalty id and customers_id are in 'customers' — Steve Price, Mar 18 '13 at 14:36
I got it to work using: SELECT gv.amount AS gv_amount, customer_latest_date.date AS h_date, c.loyalty_id AS c_loyalty_id FROM coupon_gv_customer gv INNER JOIN customers c ON gv.customer_id = c.customers_id INNER JOIN ( SELECT customer_id, MAX( DATE ) AS DATE FROM loyalty_codes_redeem_history GROUP BY customer_id )customer_latest_date ON customer_latest_date.customer_id = c.customers_id — Steve Price, Mar 18 '13 at 14:50
I'm assuming that the original INNER JOIN loyalty_codes_redeem_history h ON gv.customer_id = h.customer_id was causing it to still collect all fields for the given customer_id — Steve Price, Mar 18 '13 at 14:52
I don't think what you have is right -- I don't know how your query can sum up loyalty points. See my edit. — O. Jones, Mar 19 '13 at 00:36

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

1 Answers1