Need a JOIN Query

[reefland]

I know nothing about JOIN queries and the more I read the worse off I am. Here is what I need.

I have 2 tables, one called post and one called thread. When a new "thread" is posted it is assigned a threadid (that is the column name). When a post is replied to the thread, the post table gets the threadid in a column called tid along with other information. I need a query that will do the following:

1. For each threadid in table thread, give me the total posts for that thread in the post table.
2. Give me the last (most recent) row for each threadid=tid

Here is the query I have tried (with several iterations) with no luck:

PHP Code:

$threads = mysql_query("SELECT t2.username, t2.userid, t2.posttitle, t2.posttext, t2.tid, t2.timestamp FROM post AS t2 INNER JOIN thread AS t1 ON t2.tid = t1.threadid WHERE t1.pluserid = '$_GET[userid]'"); 


Any JOINERS out there got any ideas??

[sabret00the]

PHP Code:

 $threads = mysql_query("
SELECT t2.username, t2.userid, t2.posttitle, t2.posttext, t2.tid, t2.timestamp
FROM post AS t2
LEFT JOIN thread AS t1 ON t2.tid = t1.threadid
WHERE t1.pluserid = " . trim(intval($_GET[userid])) . "
ORDER BY t2.timestamp DESC
"); 


that should work perfectly.

[Marco van Herwaarden]

What is the use of the join with the thread table?