Write a SQL query to MySQL DBMS, which will select user names and their last comments from the database (one comment per user), with the following table structure:

CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `balance` decimal(10,2) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; CREATE TABLE `comments` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL, `text` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

I had this solution here, but it doesn’t fit a bit. It only displays users who have a comment. It is necessary to display all users with their last comment , and if anyone has a comment, the line “no comments” was returned.

  SELECT uc.name, c.text FROM comments c, ( SELECT u.id, u.name, MAX(c.id) as last_cid FROM users u LEFT JOIN comments c ON c.user_id = u.id GROUP BY u.id )uc WHERE c.user_id = uc.id AND c.id = uc.last_cid";
  • why don't you make a left join to your subquery? those. something like select name, text from comments left join (вложенный запрос) - Bald
  • I didn’t understand you very well. Could you give a complete example - Kdes70
  • look at the updated version of the request, it seems like it should work now - Bald

1 answer 1

 select u.name, c.text from users u left join( select Comments.user_id, max(Comments.id) as last_cid, from comments group by comments.user_id )uc on u.id=uc.user_id left join comments c on c.id=uc.last_cid

those. we select all users, with the subquery we get the last comments of the user, we make the left external connection to the subquery and thus we get what we need

PS I leave null replacement to you. maybe you can improve your query and me more experienced colleagues

  • The query displays an error. SQL error (1054): Unknown column 'u.id' in 'field list' - Kdes70
  • @ Kdes70 typo: I forgot the aliases in the attached request commentc & users - Bald
  • you were right in not sure max (c.text), the query displays not the last comment but the longest (( - Kdes70
  • Thank you so much!!! everything works right - Kdes70
  • @ Kdes70 is not at all. I recommend paying attention to the sql-ex at one time this resource helped me a lot - Bald