Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Question Unanswered: Help with complex inner join

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2009
    Posts
    2
    You're just genius, man! Tahnks a lot

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •