I hope all of you are in good health.

I am working on a project like a social website. I have two tables named: posts and post_replies.

I want to show the Updated Posts. Updated Posts here means the posts that are created recently or replied recently using the above mentioned two tables.

I am showing the structure of both tables below:

Table: posts
id, title, added_on

Table: post_replies
id, post_id, comment, added_on
(post_id is foreign key of id in posts)

So ....

To fetch the recently created posts I have written the below query:

SELECT id, title FROM posts ORDER BY added_on DESC

To fetch the recently replied posts I have written the below query:

SELECT id, comment FROM post_replies GROUP BY post_id ORDER BY added_on DESC

My question is that how can i combine both the above queries to fetch the latest posts based on creation date and the datetime on base of which the comment is added on them. It just like social website where we also seen the posts that are replied recently by someone and created recently by someone.

Please help.

CodePudding user response：

What i suggest add 1 more field in posts table like updated_on, so when you create post make entry in updated_on same as added_on, now when someone comment then update date in updated on as well, change your query like:

SELECT id, title FROM posts ORDER BY updated_on DESC