mysql find recent user comments

Question

I have 2 tables: user and comment.

create table user(
  userID int auto_increment,
  userName varchar(10),
  userCreatedDate timestamp,
  primary key(userID)
);

create table comment(
  commentID int auto-increment,
  userID int,
  comment varchar(100),
  primary key(commentID),
  foreign key(userID) references user(userID)
);

And I want my output like the following:

I want to find recent comment from the database: However, I tried like this and cannot get my output:

select u.userID, c.commentID, u.userCreateDate
from comment c
left join userID u on c.userID = s.userID
order by u.userCreateDate desc

How should I modify so that I can get my output?

Answer 1

You don't want to order it by the userCreateDate from the user table, because that will be the same for all comments. It would have been nice for you to have a commentCreateDate but since you don't have that you can order it by the commentID.

Change

order by u.userCreateDate desc

to

order by c.commentID desc

Edited to add:

You also need to change your select to get the actual comment.

select u.userID, c.commentID, u.userCreateDate

should be

select u.userID, c.commentID, c.comment, u.userCreateDate

to get your desired output. Plus I don't think you want to include userCreateDate in the output as people could confuse that with the date the comment was written.