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?

Tim Burch · Accepted Answer · 2014-02-05 14:41:30Z

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;

answered Feb 5, 2014 at 14:41

Tim Burch

1,0937 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alan Kael Ball Over a year ago

Hi, thanks, this is excellent. It does almost everything I wanted. Up until the ORDER BY where there is an error. I'll have to fiddle with it and see what i can do :) Edit: I just dropped the r. alias and it worked ^^

Tim Burch Over a year ago

Oops, sorry about that. As an alternative, you could wrap the second part of the UNION query with parenthesis, in order to apply the sorts only to the replies.

Collectives™ on Stack Overflow

MYSQL JOIN query results

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related