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