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

This is my accounts table:

Accounts

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

Accounts_Messages

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!

CodePudding user response：

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

CodePudding user response：

this is what I understand by your question you need to sort the accounts by the id of the accounts_Message

I think you should try

SELECT acc.* FROM Accounts_Message am LEFT OUTER JOIN Accounts acc USING(id) ORDER BY id ASC

if both Accounts and Accounts_Message has same column name id use USING(id) or the column name is different use ON acc.id = am.id instead