Sort the results of the first table by the id of the second

Question

I'm sorting some users by id (oldest to newest).

This is my accounts table:

Accounts

id	name
1	James
2	Kirk
3	Roberto
4	Lars

However, I need to improve this ordering by relating a second messages table.

Accounts_Messages

id	sender_id	receiver_id
1	1	4
2	1	2
3	1	3

In this case, users in Accounts table should be ordered by last messages.
This is the expected result:

Roberto;
Kirk;
Lars;

The question is: How can I sort the results of the first table by the id of the second?

I read that I need to use `JOIN` to relate these two tables, but I didn't know how to apply it in this specific case.

Thank you, guys!

Answer 1

Hey use this MySQL query for your aspected result

Select Accounts.name from Accounts_Messages left join Accounts on Accounts.id = Accounts_Messages.receiver_id order by Accounts_Messages.id DESC