MySQL LIMIT 1 on second table with INNER JOIN

Question

I use this request to print a forum and all its subforums. What happens, as you would expect, is that all messages in all threads belonging to this forum are displayed. What I would like is just the first entry from each thread displayed along with the name of the forum.

Query:

SELECT tf_threads. *, Tf_posts. * 
    FROM tf_threads INNER JOIN tf_posts 
    ON tf_threads.thread_id = tf_posts.thread_id 
        AND tf_threads.parent_id = 54 ORDER BY tf_posts.date ASC

Please note that the field parent_idsets the variable in the real request.

So. If I make sense, can someone help me find out which request to write to select only the first message from each thread?

If there are no simple (ish) answers, how can I do this if I used the column field in the second table, for example, the first message in the stream is number 1, the second message is number 2, etc. If I use this method, I would only like to select messages with a counter number field of 1. I could just expand the original request with the help AND post_number=1on the right?

Thanks for reading,

James

Answer 1

Something like that?

http://murrayhopkins.wordpress.com/2008/10/28/mysql-left-join-on-last-or-first-record-in-the-right-table/

Edit: I think it should be something like this, but I'm not a SQL expert either:

SELECT tf_threads.*, tf_posts_tmp.*
FROM tf_threads
LEFT JOIN (SELECT p1.*
           FROM tf_posts as p1
           LEFT JOIN tf_posts AS p2
           ON p1.postid = p2.postid AND p1.date < p2.date) as tf_posts_tmp
ON (tf_threads.thread_id=tf_posts_tmp.thread_id)