MYSQL JOIN query results

Question

I have an old forum I built many years ago that i'm turning into a read-only section on the website. As an exercise in programming i'm attempting to cut down the amount of code used despite the terrible database design.

What I am trying to achieve is on the reply page: a single query to show the first post (stored stupidly on the 'topics' table) and then the rest of the posts from the 'replies table.

Abridged tables:

'replies'
topicID
posted_by
body
date_posted

'topics'
topicID
subject
body
posted_by
date_posted

The form I'm trying to get is:

Initial Post from 'topics' followed by replies sorted by date_posted (oldest first).

here's the query I've been fiddling with:

SELECT DISTINCT
r.body, r.posted_by, r.date_posted, t.body, t.date_posted, t.posted_by, t.subject
FROM
replies r
LEFT JOIN topics t
    ON r.topicID = t.topicID
WHERE
r.topicID = 2372
ORDER BY
t.posted_by DESC,
r.date_posted DESC

Has anyone got any ideas on how to tweak this to get my desired scheme?

Answer 1

A UNION query should provide the data you're looking for:

SELECT topicID, subject, body, posted_by, date_posted
FROM topics
WHERE topicID = 2372
UNION
SELECT r.topicID, t.subject, r.body, r.posted_by, r.date_posted
FROM replies r
    INNER JOIN topics t ON r.topicID = t.topicID
WHERE t.topicID = 2372
ORDER BY r.date_posted DESC;