Single SQL to aggregate data from three tables

Asked Dec 09 '14 at 20:14

Active Dec 09 '14 at 20:26

Viewed 39 times

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.

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!

edited Dec 09 '14 at 20:26

Strawberry

33,750
13
40
57

asked Dec 09 '14 at 20:14

Cosmin

2

How do you define the "latest" address? – Gordon Linoff Dec 09 '14 at 20:16
By the higher address_id. – Cosmin Dec 09 '14 at 20:17
1

SELECT x.* FROM users_adresses JOIN (SELECT user_id, MAX(address_id) max_address_id FROM users_addresses GROUP BY user_id) y On y.user_id = x.user_id AND y.max_address_id = x.address_id; – Strawberry Dec 09 '14 at 20:19
@Strawberry, the country name must also be present in the results. – Cosmin Dec 09 '14 at 20:29
I think you can figure that bit out – Strawberry Dec 09 '14 at 20:33
Actually, I can't even figure it out how can I get the first_name, last_name and username fields :( – Cosmin Dec 09 '14 at 20:44

Single SQL to aggregate data from three tables

0 Answers0