CODEWITHSUNDEEP
mysql
Kacy
Kacy

Reputation: 3430

Select most recent entries and join with another table

My sql's rusty. I need to select the 30 most recent messages (those with the 30 largest id's), and then use the sender_id to join with the users table id. In English, I want the users who sent the last 30 messages.

Here my query (which doesn't run):

SELECT * FROM group_messages
WHERE group_id=52
ORDER BY id DESC
LIMIT 30
LEFT JOIN users
ON users.id=group_messages.sender_id

If there's a better approach to this kind of query, then by all means.

Note: The first part works in selecting the 30 most recent messages. The trouble came when I tried joining on the users table. (And I just realized even if this query did run, I would need to add GROUP BY users.id a user may have sent more than 1 of the 30 most recent messages.

Upvotes: 0

Views: 34

Answers (1)

Barmar
Barmar

Reputation: 780974

The JOIN clause has to come before WHERE, ORDER BY, and LIMIT

SELECT DISTINCT id
FROM (
    SELECT u.id
    FROM group_messages AS g
    INNER JOIN users AS u on u.id = g.sender_id
    WHERE group_id = 52
    ORDER BY id DESC
    LIMIT 30) AS x

I put it in a subquery so I could then perform the DISTINCT selection. If you do that in the same query, it will get rid of the duplicates before selecting the most recent 30 rows, so you'll get the 30 most recent senders, not the senders of the 30 most recent messages.

I doubt you really need a LEFT JOIN instead of INNER JOIN. That would only be needed if a message could have a sender ID that isn't in users.

Upvotes: 2

Related Questions