CODEWITHSUNDEEP
phpmysqlsqljoinsql-order-by
Henk de B
Henk de B

Reputation: 55

MySQL Query join last comment to topic

I have a topic and topic_comments table. I want to join them both.

+------------+ +------------+
| Topic      | | Comments   |
+------------+ +------------+
| id         | | parent_id  |
| title      | | content    |
| createdate | | createdate |
| content    | | creator_id |
+------------+ +------------+

The join is on topic.id = topic_comments.parent_id. I want to show the topic with latest comment and order by latest comment createdate. And not show duplicate topics. Can anyone help me?

So far I have this:

select p.id, p.title, p.createdate, p.content, p.int_0 as reacties_total, p.char_1 as prio, p.char_0 as status, r.createdate as r_createdate, r.creator_id as r_creator_id, r.content as r_content
from pages p, topic_reacties r
where r.parent_id = p.id
  and p.parent_id = ' . $this->id . ' 
order by p.int_2 desc

This however doesn 't show topics without comments. It only returns topics with reactions.

Upvotes: 0

Views: 628

Answers (2)

TommCatt
TommCatt

Reputation: 5636

Do you want to show topics with the latest comment and sorted by comment or every topic whether it has a comment or not? Those are two different requirements.

If you want to show topics with the latest comment, use the join just as you did (except please lose the archaic syntax), but add more detail to the on clause:

select  p.id, p.title, p.createdate, p.content,
        p.int_0 as reacties_total, p.char_1 as prio,
        p.char_0 as status, r.createdate as r_createdate,
        r.creator_id as r_creator_id, r.content as r_content
from    pages p
join    topic_reacties r
    on  r.parent_id = p.id
    and r.createdate =(
        select  Max( createdate )
        from    topic_reacties
        where   parent_id = r.parent_id )
order by r.createdate;

Don't let the subquery scare you. Test it and you will see impressive efficiency -- if the createdate column is indexed.

If you want all topics whether they have a comment or not but those with comments only the latest one, then use an outer join. You would still use the same subquery but some DBMSs don't allow subqueries in the on clause of outer joins. Oracle for instance. I'm not sure about MySQL. In any event, I always move it to the where clause just to be safe. (I deal with a lot of different DBMSs.)

However, when you place a check of the outer table of an outer join in the where clause, you can convert the output to be the same as an inner join. So you have to use a little trick.

select  p.id, p.title, p.createdate, p.content,
        p.int_0 as reacties_total, p.char_1 as prio,
        p.char_0 as status, r.createdate as r_createdate,
        r.creator_id as r_creator_id, r.content as r_content
from    pages p
left join topic_reacties r
    on  r.parent_id = p.id
where   (r.parent_id is null
    or  r.createdate =(
        select  Max( createdate )
        from    topic_reacties
        where   parent_id = r.parent_id ))
order by r.createdate;

Note that I've placed parentheses/brackets around the where check although, as written, they are not absolutely necessary. However, if you add any other check you will need them to go outside the parentheses.

where   (r.parent_id is null
    or  r.createdate =(
        select  Max( createdate )
        from    topic_reacties
        where   parent_id = r.parent_id ))
    and p.ID = :SomeID

Check out the SQL Fiddle

Upvotes: 1

Yammi
Yammi

Reputation: 71

SELECT title,content,max(createdate) as createdate 
FROM topic 
left join comments 
on topic.id=comments.parent_id 
group by title 
order by createdate desc;

Upvotes: 0

Related Questions