How to get data from one table and the number of rows from another in one query?

Question

MySQL

There is a website that is a forum where people can ask questions. On the question page, you can like each answer. For this system to work, there are two tables in the database, answers and likes. Their architecture is:



The system works in such a way that on each page with a question, an ajax request is sent to the printAnswers.php file. In this file, using the printAnswers function from a specific class, the answers are printed. This function retrieves the data from the answers table that is needed to display the question (gets a link to the image of the user who gave the answer, his name, etc., etc.) and then, using the generateAnswerShell(userId, img_link, etc.) function, generates an answer shell (this function simply generates html code with user data). The main problem here is getting the number of likes for a particular answer. I tried to solve the problem in such a way that in printAnswers send a second sql query to the likes table and just output the number of cells where the answer ID matched the answer ID, sent in the request using COUNT(*), but this greatly complicates the work, because in 1 method you need to send two requests. Is it possible to organize the Likes cell in the answers table so that the counting happens automatically already in the database? Something like this, only I don't know what the correct syntax is:

ALTER TABLE answers ADD LIKES = COUNT(*) IN likes WHERE answers.userID=likes.userID;

Answer 1

SELECT answers.*, count(*) as cnt
FROM answers
LEFT JOIN likes ON likes.answerID = answer.id
GROUP BY answer.id

Answer 2

SELECT 
    answers.*,  IFNULL(likes_count, 0) as likes_count
FROM answers
LEFT JOIN (
   SELECT answerID, count(*) as likes_count FROM likes GROUP BY answerID
) likes_count ON likes_count.answerID = answer.id