Nick Roberts Nick Roberts -4 years ago 101
SQL Question

Selecting and joining rows from multiple tables based on MAX(Column Name)

Overall, my goal is to obtain a customer's email, code, and most recent rewards balance from three tables.

The three tables are: Customer, CustomerCode, and Rewards

The tables look roughly like the following...

Customer



id email lastcode
----|---------------|-----------
000 |test@test.com | 1234test
001 |test1@test.com | 5678test
002 |test2@test.com | test1234
003 |test3@test.com | test5678


CustomerCode



id code customer
----|---------|---------
100 |1234test | 000
101 |5678test | 001
102 |test1234 | 002
103 |test5678 | 003


Rewards



customercode logdate balance
-------------|------------|--------
100 | 01/01/2016 | 1200
101 | 04/05/2016 | 40
102 | 06/22/2016 | 130
102 | 10/14/2016 | 220
103 | 12/03/2016 | 500
103 | 01/18/2017 | 750


I'm trying to gather information from all the tables linking back to the customer. I'm currently using the following SQL query but am running into some issues.

SELECT Customer.email, Customer.lastcode, CustomerCode.id, Rewards.balance, MAX(Rewards.logdate)
FROM Customer
JOIN CustomerCode ON Customer.lastcode=CustomerCode.code
JOIN Rewards ON CustomerCode.id=Rewards.CustomerCode
GROUP BY Customer.Email, Customer.LastCode, CustomerCode.id, Rewards.Balance


Results



As you can see, I'm getting multiple results for the same customer, but I only want to get the most recent rewards balance for each customer.

email lastcode id balance logdate
---------------|-----------|-----|----------|-----------
test@test.com | 1234test | 100 | 1200 | 01/01/2016
test1@test.com | 5678test | 101 | 40 | 04/05/2016
test2@test.com | test1234 | 102 | 130 | 06/22/2016
test2@test.com | test1234 | 102 | 220 | 10/14/2016
test3@test.com | test5678 | 103 | 500 | 12/03/2016
test3@test.com | test5678 | 103 | 750 | 01/18/2017


Is there any way I can eliminate those duplicate records and only display the most recent rewards balance?

Answer Source

You can use a correlated subquery or aggregation for this:

SELECT c.email, c.lastcode, cc.id, r.balance, r.logdate
FROM Customer c JOIN
     CustomerCode cc
     ON c.lastcode = cc.code JOIN
     Rewards r
     ON cc.id = r.CustomerCode JOIN
     (SELECT r.CustomerCode, MAX(r.logdate) as max_logdate
      FROM Rewards r
      GROUP BY r.CustomerCode
     ) rr
     ON rr.CustomerCode = r.CustomerCode AND rr.max_logdate = r.logdate;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download