There are 3 tables: notes, users and comments. Links:
notes.author_id - users.id
comment.note_id - notes.id
When selecting notes from notes, you need to get the name of the author from users (you figure it out) and count the number of comments to the note. How to do it?

 SELECT 
   notes.id,
   notes.title,
   notes.content
   users.username
  - COUNT (comments.id) AS comments_count
 FROM 
   notes
 INNER JOIN 
   users 
 ON 
   (notes.author_id = users.id)
 - ???
 ORDER BY 
   datetime 
 Desc

    1 answer 1

    I think the easiest way is with a subquery, right in the select list, so that the records do not get mixed up and do not write group by.

    SELECT notes.id, notes.title, notes.content, users.username, (SELECT count(1) FROM comment WHERE comment.note_id=notes.id) AS comments_count FROM notes JOIN users ON notes.author_id=users.id ORDER BY datetime DESC
    • Oh, thank you very much! I didn’t think of something about an attached request)) - Vlad