1

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!

1
  • 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, 2014 at 18:20

2 Answers 2

2

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

1

I know this is an old question but in MySQL version 8 and above we have window functions. I'm quite new to them so I thought I'd practice converting the MAX ... GROUP BY approach into a ROW_NUMBER() .. PARTITION BY approach.

See db-fiddle at https://www.db-fiddle.com/f/wtmsW7J65nRsA2euNmWR27/0

I tell a tale of four brothers. Joe moved from one street to another, Moe fell on hard times and had to move in with his parents, Harry took up mining and got a penthouse in the mines of Moria (don't ask) and Fred has always just lived in Moria.

CREATE TABLE users (
  `user_id` INTEGER,
  `username` VARCHAR(5)
);

INSERT INTO users
  (`user_id`, `username`)
VALUES
  ('1', 'Joe'),
  ('2', 'Moe'),
  ('3', 'Harry'),
  ('4', 'Fred');

CREATE TABLE users_address (
  `address_id` INTEGER,
  `user_id` INTEGER,
  `street` VARCHAR(18),
  `country_id` INTEGER
);

INSERT INTO users_address
  (`address_id`, `user_id`, `street`, `country_id`)
VALUES
  ('1', '1', 'Joe''s street', 1),
  ('2', '1', 'Joe''s new street', 1),
  ('3', '2', 'Moe''s address', 2),
  ('4', '2', 'Moe''s mums house', 2),
  ('5', '3', 'Harry''s mums house', 3),
  ('6', '3', 'Harry''s penthouse', 4),
  ('7', '4', 'Fred''s dungeon', 4);

CREATE TABLE countries (
  `country_id` INTEGER,
  `name` VARCHAR(22)
);

INSERT INTO countries
  (`country_id`, `name`)
VALUES
  ('1', 'United Kingdom of Dork'),
  ('2', 'USA USA USA'),
  ('3', 'Fintechland'),
  ('4', 'Moria');

and the query:

SELECT u.user_id, username, street, country_name
FROM users u
INNER JOIN (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY address_id DESC) AS address_seq,
    user_id, address_id, street, c.name as country_name
    FROM users_address ua
    INNER JOIN countries c ON ua.country_id = c.country_id
  ) address_window ON address_window.user_id = u.user_id
  WHERE address_window.address_seq = 1

Gives the results:

user_id username street country_name
1 Joe Joe's new street United Kingdom of Dork
2 Moe Moe's mums house USA USA USA
3 Harry Harry's penthouse Moria
4 Fred Fred's dungeon Moria

(Note: if the join against addresses might fail, e.g. in your schema there are no rows in users_address for a given user, then the window operation will return NULL in address_seq, thus you would change the join against the window result to LEFT OUTER JOIN (so that nulls may be returned) and say WHERE (address_window.address_seq = 1 OR address_window.address_seq IS NULL) - extra brackets added to separate that boolean test from any other clauses you may have in your WHERE clause. See https://www.db-fiddle.com/f/wtmsW7J65nRsA2euNmWR27/1 with an added 'Tom' user with no address records.)

As in the other answer above, this replaces the use of aggregate functions MAX and GROUP BY, and the need to join against the users_address table twice and the countries table outside of the users_address table operations, in the alternate form of the same query:

https://www.db-fiddle.com/f/jmXwK948MmUQ1yUx4YhKob/0

SELECT u.user_id, username, street, c.name AS country_name
FROM users u
INNER JOIN (
  SELECT
    MAX(address_id) AS address_id, user_id
    FROM users_address ua
    GROUP BY user_id
  ) latest_address ON latest_address.user_id = u.user_id
INNER JOIN users_address ua ON latest_address.address_id = ua.address_id
INNER JOIN countries c ON ua.country_id = c.country_id

Hopefully this will help people like me, fishing around for an example of converting old style aggregate joins into more modern window operations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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