CODEWITHSUNDEEP
phpsql
lawls
lawls

Reputation: 1508

Get last forum post

I'm creating a forum, where I have all main thread posts in one database table, and all answers in another. So far, it's been a hell to have two tables.

forum_threads
thread_id | title | post

forum_answers
answer_id | ref_thread_id (foreign key) | post

How do I select the latest post, including the threads title? The thread might not have any answer, in which case all data should come from forum_thread. But if it has an answer data should be selected from forum_answers and also the title which is found in forum_threads.

Hope you understand!

This would be way easier if I had it all in one table, answers and main posts. Although I was told this was the best way, not so sure..... I'm considering switching back to only one table.

Thanks for your help!

Upvotes: 0

Views: 96

Answers (2)

user985189
user985189

Reputation:

This should get you what you want, but you should really store dates in those tables.

SELECT
    ft.thread_id,
    ft.title,
    COALESCE(fa.post,ft.post) AS post
FROM
    forum_threads ft
LEFT JOIN
    forum_answers fa
    ON  (ft.thread_id = fa.ref_thread_id)
ORDER BY
    ft.thread_id
LIMIT 1;

It will give you the post value from forum_answers unless there isn't one for that thread, then it will just give you the post value from forum_threads

Upvotes: 1

Talha Ahmed Khan
Talha Ahmed Khan

Reputation: 15453

Try this.

SELECT fa.post, ft.title
FROM forum_threads ft INNER JOIN forum_answers fa ON
     ft.thread_id = fa.ref_thread_id
ORDER BY fa.answer_id
LIMIT 1;

Upvotes: 0

Related Questions