我有一个topic和topic_comments表。我想加入他们俩。
+------------+ +------------+
| Topic | | Comments |
+------------+ +------------+
| id | | parent_id |
| title | | content |
| createdate | | createdate |
| content | | creator_id |
+------------+ +------------+
联接位于
topic.id = topic_comments.parent_id上。我想显示具有最新评论和按最新评论createdate排序的主题。并且不显示重复的主题。谁能帮我？到目前为止，我有这个：
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
但是，这不会显示没有注释的主题。它只返回带有反应的主题。
最佳答案
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;