CODEWITHSUNDEEP
mysqlinner-join
Bojangles
Bojangles

Reputation: 101483

MySQL LIMIT 1 on second table with INNER JOIN

I am using this query to print out a forum board and all it's sub forums. What happens, as may be expected, is that all posts in all threads belonging to that forum are displayed. What I would like to happen is only the first post from each thread is displayed along with the forum title.

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 the parent_id field is given a variable in the real query.

So. If I make sense, can anyone help me out as to what query to write to only select the first post from each thread?

If there are no simple(ish) answers, how could I do it if I used a post number field in the second table, for example, the first post in thread has number 1, second post has number 2, etc. If I use this method, I'd obviously only like to select posts with a count number field of 1. I could just expand the original query with a AND post_number=1 right?

Thanks for reading,

James

Upvotes: 3

Views: 2175

Answers (1)

thejh
thejh

Reputation: 45568

Something like this?

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

Edit: I think that it has to be something like this, but I'm also not an SQL expert:

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)

Upvotes: 2

Related Questions