SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru DeNasio's Avatar
    Join Date
    May 2001
    Posts
    830
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    INNER JOIN 2x on same table

    Hello,

    I'm writting my own forum script, something like the Sitepoint Forums.

    I have a table that contains the forum Topics:

    Code:
    topicid   - INT(10) UNSIGNED PRIMARY_KEY AUTO_INCREMENT NOT_NULL
    forumid   - INT(10) INDEX UNSIGNED NOT_NULL
    firstpost - INT(10) UNSIGNED NOT_NULL
    lastpost  - INT(10) UNSIGNED NOT_NULL
    replies   - INT(10) UNSIGNED NOT_NULL
    views     - INT(10) UNSIGNED NOT_NULL
    The field firstpost contains the post id of the first post in the topic. The field lastpost contains the post id of the last post in the topic.

    I have a table that contains the forum Posts:

    Code:
    postid   - INT(10) UNSIGNED PRIMARY_KEY AUTO_INCREMENT NOT_NULL
    topicid  - INT(10) INDEX UNSIGNED NOT_NULL
    forumid  - INT(10) INDEX UNSIGNED NOT_NULL
    subject  - VARCHAR(100) NOT_NULL
    message  - TEXT NOT_NULL
    username - VARCHAR(25) NOT_NULL
    userid   - INT(10) UNSIGNED NOT_NULL
    datetime - DATETIME NOT_NULL
    ipaddr   - VARCHAR(25) NOT_NULL
    When viewing the list of topics I want to display information about the first post (like subject, topic starter, etc.) and information about the last post (last poster, time, etc.). I use the following query to do this:

    Code:
    $result = @mysql_query("SELECT
    t.topicid,t.replies,t.views,p.subject,p.username,p.userid,q.username as lastuser,date_format(q.datetime,'%b %e, %Y %H:%i') as datetime2
    FROM Topics t INNER JOIN Posts p ON (p.postid=t.firstpost) INNER JOIN Posts q ON (q.postid=t.lastpost)
    WHERE t.forumid='$forumid'
    ORDER BY p.datetime DESC");
    What bothers me in the query above is that I have to do an Inner join on the table Posts twice to get the info I need. Is there another way to do this?
    Ballot-Box.net - free polls for webmasters
    FormLog.com - free form processor

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, that is the correct way

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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