LEFT JOIN'ing 2 tables a particular way

Nor · 01-24-2008, 04:08 PM

Sa if I have two mysql tables and the 1st one is the 'topic' table and the other is a 'reply', there are many replies and one topic for each set of replies, How could I LEFT JOIN the last reply to te topics query (when selecting the topics data from the database), without having 2 queries, is this possible, if so how would I do this.

Alan @ CIT · 01-24-2008, 04:13 PM

Just to clarify, you'll be selecting a topic from the topic table and you want to select the most recent reply to that topic from the replies table?

Alan

Nor · 01-24-2008, 04:18 PM

Yep, most recent reply to that topic. All in 1 query, So topic details and the most recent reply to that topic' details will be the result.

xenon · 01-24-2008, 07:30 PM

Code:

SELECT r.*, t.* FROM replies r LEFT JOIN topics t ON(r.topic_id=t.id) ORDER BY r.reply_added DESC LIMIT 0,1

...?

Nor · 01-24-2008, 08:25 PM

Ah, thanks I'll give that a try :).

Nor · 01-25-2008, 01:05 PM

Okay after realizing that this wasn't what I wanted when I went home, I thought I'd mention this is more as of a blog type script. My sql is set up like this atm:

Code:

$db->set_query("SELECT t.*, u.user_id,u.user_name FROM `{$config['db_prefix']}topics` t, `{$config['db_prefix']}users` u WHERE t.topic_parent = '{$id}' AND u.user_name = t.topic_author ORDER BY `topic_last_posted` DESC");

Now I just need to get the latest reply to this topic and everything will be well, i tried different approaches like GROUP BY didn't work and now I'm back here.

xenon · 01-25-2008, 06:34 PM

And why didn't my solution help you? That's what it does: it grabs info about the last reply and the topic associated with it.

Salathe · 01-25-2008, 07:22 PM

It sounds more like he wants to query for the latest topics and bring back the latest reply to each of those topics in a joined result as opposed to querying for the latest reply and joining the topic -- a subtle but important difference.

I'm not entirely sure that what is being asked for can be done with a single query (prove me wrong

).

Nor · 01-25-2008, 08:17 PM

actually i'm grabbing all topics in a forum and wanting to get the latests reply for that topic in 1 query.