Select from the same table for two different values (mysql)

Question

I am making a small forum. What i want is when listing the topics of a forum, to show the username and avatar of both of the person who made the topic and of the person who has last replied.

The current query (which currently only finds the avatar+username of the creator of topic) is:

SELECT 
    forum_topics.primkey,
    forum_topics.title,
    forum_topics.creatorid, 
    forum_topics.last_reply_poster, 
    users.username,
    users.avatar,
    forum_tracks.lastmark 
FROM 
    users,
    forum_topics 
    LEFT JOIN forum_tracks 
        ON forum_tracks.userid='".$_SESSION['loggeduserkey']."' 
        AND forum_tracks.topic_id=forum_topics.primkey 
WHERE 
    forum_topics.cat_id='".$forum_id."' 
    AND users.userkey=forum_topics.creatorid 
ORDER BY ...;

So, how can i modify the above query so to find also the username+avatar of last_reply_poster(id of the user)?

Thanks!

Brian Fisher · Accepted Answer · 2011-05-15 15:59:02Z

2

You have to join the users table in twice:

SELECT 
    forum_topics.primkey,
    forum_topics.title,
    forum_topics.creatorid, 
    forum_topics.last_reply_poster, 
    users.username,
    users.avatar,
    u2.username,
    u2.avatar,
    forum_tracks.lastmark 
FROM 
    users,
    users as u2,
    forum_topics 
    LEFT JOIN forum_tracks 
        ON forum_tracks.userid='".$_SESSION['loggeduserkey']."' 
        AND forum_tracks.topic_id=forum_topics.primkey 
WHERE 
    forum_topics.cat_id='".$forum_id."' 
    AND users.userkey=forum_topics.creatorid
    AND u2.userkey=forum_topics.last_reply_poster
ORDER BY ...;

answered May 15, 2011 at 15:59

Brian Fisher

24.1k15 gold badges80 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select from the same table for two different values (mysql)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related