0

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!

  • The reference to the docs that appears here among other places is very important for implementing GROUP BY. Not thinking of this rule has caught me many times because queries are still well-formed but behave unexpectedly. – WAF Dec 11 '14 at 18:20
1

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 !!!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.