"Michal Stankoviansky" <michal.stankoviansky@gmail.com> wrote in message
news:1135962374.353170.198980@g14g2000cwa.googlegr oups.com...
> I want to get a resultset where each row would represent one discussion
> topic, with username of the user who created the topic (posted the
> first post of the topic and specified a topic subject), with text of
> this first post and in the same row username, text of the post and time
> of the post that is the newest reply in the topic.

Hmm. Maybe something like this (I tested this with your sample data):

SELECT bt.topic_subject,
b1.id, b1.post_text, b1.time_posted, u1.username,
COALESCE(b2.id, b1.id) AS last_posting_id,
COALESCE(b2.post_text, b1.post_text) AS last_post_text,
COALESCE(b2.time_posted, b1.time_posted) AS last_time_posted,
COALESCE(u2.username, u1.username) AS last_username
FROM board AS b1
INNER JOIN users AS u1 ON b1.user_id = u1.id AND b1.answer_to = 0
INNER JOIN board_topics AS bt ON b1.id = bt.id
LEFT OUTER JOIN board AS b2 ON b1.id = b2.answer_to
AND b2.time_posted >= ALL(
SELECT bsub.time_posted
FROM board AS bsub
WHERE bsub.answer_to = b1.id)
LEFT OUTER JOIN users AS u2 ON b2.user_id = u2.id
ORDER BY b2.time_posted DESC, b2.id DESC

The COALESCE() function calls handle the case when the thread consists of
only one posting. The results of b2 will be NULL if no postings exist with
answer_to equal to the id of the first posting in the article.

BTW, I would have designed the schema a bit differently:
- Don't use "id" as a field name. Joins are more clear if you're comparing
user_id to user_id, post_id to post_id, and topic_id to topic_id.
- The answer_to should reference the posting to which the current posting is
in reply to, not the first posting in the thread. In your current design,
you lose the ability to trace branches of the discussion.
- Create an additional field in board to reference board_topics.topic_id.
That's a better way to track the topic.
- Use NULL as the answer_to signifier for a topic's first posting, instead
of the value 0. Zero is a valid integer value, so using it as a signifier
means you have to prime the table carefully.

Another suggestion is to evaluate phpBB (www.phpbb.com) which is an
open-source bulletin board written for PHP and MySQL. Many hosting service
providers support phpBB (e.g. www.101hosting.com). If it does what you
need, it'd be easier to use finished software than to spend many days
implementing your own. I understand that such software often lacks a
particular feature that you need, but it still may be easier to enhance
working BB software than to implement your own.

Regards,
Bill K.