CODEWITHSUNDEEP
mysqlsql
Kaka
Kaka

Reputation: 395

Get latest entry and join another table

I'm trying to get the username and timestamp of the most recent post where topicID is, for example, 88.

Users

id      |  username    
--------|----------
45234   | kaka   
32663   | lenny  
52366   | bob

Posts

id      |  message  | topicID | timestamp | userID
--------|-----------|---------|-----------|-------
675     | hello     | 88      | 100       | 32663
676     | hey       | 88      | 200       | 45234
677     | howdy     | 88      | 300       | 52366

So here I would want postID 677 and user bob.

Can I do this in a single sql query?

Would be great if I could implent it into this:

SELECT topics.id, topics.subject, topics.forum_id
FROM topics WHERE topics.forumID = 16

Upvotes: 0

Views: 62

Answers (2)

Keith
Keith

Reputation: 528

Untested, but off the top of my head, I think the following query will get you what you want:

SELECT Users.username, Posts.timestamp
FROM Users JOIN Posts on Users.id = Posts.userID
WHERE Posts.topicID = 88
ORDER BY Posts.timestamp DESC
LIMIT 1

Upvotes: 1

John Woo
John Woo

Reputation: 263723

Assuming that table Topic is linked with table Post by Topic.ID = Post.TopicID and you want to get the latest post associated with it, you can have a subquery which basically gets the latest id (assuming it's set as auto-incremented column) for each topicID and join the result on table Post to get the other columns. Also you need to join on table User in order to get the name of the user who posted the entry.

SELECT  a.id, 
        a.subject, 
        a.forumid,
        b.message,
        b.timestamp,
        d.username
FROM    topic a
        INNER JOIN Posts b
            ON a.id = b.topicID 
        INNER JOIN
        (
            SELECT  topicID, MAX(id) id
            FROM    Posts
            GROUP   BY topicID
        ) c ON b.topicID = c.topicID AND
                b.id = c.ID
        INNER JOIN users d
            ON b.userID = d.id
WHERE   a.forumID = 16

if you remove the WHERE clause, you will get all the latest entry for each forumID.

Upvotes: 1

Related Questions