Help with complex inner join

mrcs · #1 (**permalink**) 11-15-09, 11:36

Hi, members,

I'm working on simple forum and I can't make one query.

I want to show on homepage list of topics where will be the author of the topic and author of last post.

I can make any of them but not together, because there is double inner join to one table (one for topic author and second for last post's author).

Tables (just with necesary parts):

Topics
######
id
author_id (will be connected directly to Users.id)
last_post_id (will be connected to Posts.id)

Posts
#####
id
author_id (connected to Users.id)

Users
#####
id
name

I can start with this and I don't know how to continue:

Code:

SELECT topics.author_id, topics.last_post_id, users.name, 
FROM topics
INNER JOIN users ON topics.author_id = users.id

It will give back topic's names, their authors and ID of last post. And I want to know last post's author's name.

Can anyone help, please?

Thanks for any response

r937 · #2 (**permalink**) 11-15-09, 14:45

Code:

SELECT topicauthor.name AS topic_author_name
     , lastposter.name AS last_poster_authr_name
  FROM topics
INNER 
  JOIN users AS topicauthor 
    ON topicauthor.id = topics.author_id
INNER
  JOIN posts
    ON posts.id = topics.last_post_id
INNER
  JOIN users as lastposter
    ON lastposter.id = posts.author_id

mrcs · #3 (**permalink**) 11-15-09, 14:52

You're just genius, man! Tahnks a lot

I haven't heard about "table alias" in Inner join. Thanks for fast help!

Internet Services

Web Development

Digital Marketing

Consumer Tech