INNER JOIN 2x on same table

[DeNasio]

Hello,

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

I have a table that contains the forum Topics:

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:

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:

$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?

[r937]

no, that is the correct way