Mysql – SQL query to aggregate data from three tables

join;MySQL

I have the following three tables:

The "users" table:

user_id
first_name
last_name
username
password

The "users_addresses" table:

address_id
user_id
street
country_id
email

The "countries" table

country_id
name

A user can have multiple addresses, but each user has at least one address.

What I would like to do is to write a single query to get all the users with their latest address (the address with the higher address_id for that user_id).

I tried this query, but apparently, it doesn't return the latest address if there's more than one address for a user:

SELECT u.first_name
     , u.last_name
     , u.username
     , u.password
     , a.user_id
     , a.street
     , a.email
     , c.name 
  FROM users u 
  LEFT 
  JOIN user_addresses ua 
    ON ua.user_id = u.user_id 
  LEFT 
  JOIN countries c 
    ON ua.country_id = c.country_id 
 GROUP 
    BY a.user_id 
 ORDER 
    BY u.last_name ASC

Any help is appreciated.

Thank you!

Best Answer

You need to get the maximum address_id per user_id

SELECT user_id,MAX(address_id) address_id
FROM users_addresses GROUP BY user_id;

Make that query a subquery and join back to the other tables

SELECT u.first_name
 , u.last_name
 , u.username
 , u.password
 , a.user_id
 , a.street
 , a.email
 , c.name 
FROM
(SELECT user_id,MAX(address_id) address_id FROM users_addresses GROUP BY user_id) ua
INNER JOIN users u           ON ua.user_id    = ua.user_id
INNER JOIN users_addresses a ON ua.address_id = a.address_id
INNER JOIN countries c       ON a.country_id  = c.country_id
ORDER BY u.last_name,u.firstname
;

Give it a Try !!!

Related Question