SQL left join: how to return the newest from tableB and grouped by another field

Question

I've been trying for two days, without luck.

I have the following simplified tables in my database:

customers:

| id | name     |
| 1  | andrea   |
| 2  | marco    |
| 3  | giovanni |

access:

| id | name_id | date |
| 1  | 1       | 5000 |
| 2  | 1       | 4000 |
| 3  | 2       | 1500 |
| 4  | 2       | 3000 |
| 5  | 2       | 1000 |
| 6  | 3       | 6000 |
| 7  | 3       | 2000 |

I want to return all the names with their last access date.

At first I tried simply with

SELECT * FROM customers LEFT JOIN access ON customers.id = 
access.name_id

But I got 7 rows instead of 3 as expected. So I understood I need to use GROUP BY statemet as the following:

SELECT * FROM customers LEFT JOIN access ON customers.id = 
access.name_id GROUP BY customers.id

As far I know, GROUP BY combines using a random row. In fact I got unordered access dates with several tests.

Instead I need to group every customer id with its corresponding latest access! How this can be done?

I'm not sure I understand correctly what you'd like to achieve here but I'm having the impression you might be misunderstanding how the LEFT JOIN works. The GROUP BY will work if you have an aggregation function like MAX or MIN. Can you explain better what's the output you're expecting to see? — Stefano, Nov 18 '17 at 12:32
@decadenza . . . The use of `SELECT *` with `GROUP BY` is a broken-ness that has happily been fixed in the most recent versions of MySQL. That is not the right way to do what you want. — Gordon Linoff, Nov 18 '17 at 13:00

Stefano · Answer 1 · 2017-11-18T12:53:33.013

I think this is what you'd like to achieve:

SELECT c.id, c.name, max(a.date) last_access
FROM customers c 
LEFT JOIN access a ON c.id = a.name_id 
GROUP BY c.id, c.name

The LEFT join will return all entries in table customers regardless if the join criteria (c.id = a.name_id) is satisfied. This means that you might get some NULL entries.

Example:

Simply add a new row in the customers table (id: 4, name: manuela). The output will have 4 rows and the newest row will be (id: 4, last_access: null)

cdaiga · Accepted Answer · 2017-11-18T13:09:25.107

1

You have to get the latest date from the access table with a group by on the the name_id, then join this result with the customer table. Here is the query:

select c.id, c.name, a.last_access_date from customers c left join
(select id, name_id, max(access_date) last_access_date from access group by name_id) a
on c.id=a.name_id;

Here is a DEMO on sqlfiddle.

edited Nov 18 '17 at 13:09

answered Nov 18 '17 at 12:49

cdaiga

4,861
3
22
42

score 1 · Answer 3 · answered Nov 18 '17 at 12:56

I would do this using a correlated subquery in the ON clause:

SELECT a.*, c.*
FROM customers c LEFT JOIN
     access a
     ON c.id = a.name_id AND
        a.DATE = (SELECT MAX(a2.date) FROM access a2 WHERE a2.name_id = a.name_id);

If this statement is true:

I need to group every customer id with its corresponding latest access! How this can be done?

Then you can simply do:

select a.name_id, max(a2.date)
from access a
group by a.name_id;

You do not need the customers table because:

All customers are in access, so the left join is not necessary.
You need no columns from customers.

SQL left join: how to return the newest from tableB and grouped by another field

3 Answers3