There are 2 tables in the MySQL database (photos and comments to them):

CREATE TABLE `photos` ( `photo_id` int(11) NOT NULL, `photo_title` tinytext NOT NULL, PRIMARY KEY (`photo_id`) ) ENGINE=InnoDB DEFAULT CHARSET=cp1251; CREATE TABLE `comments` ( `comment_id` int(11) NOT NULL AUTO_INCREMENT, `photo_id` int(11) unsigned NOT NULL, `user_id` int(11) unsigned NOT NULL, `comment_text` text NOT NULL, PRIMARY KEY (`comment_id`) ) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

You need to create a SQL query that displays a list of photos, indicating the number of comments, sorted in descending order of their number, which have less than 5 commentators.

  • one
    @Pferdeficker, According to the rules of the forum, questions should not be limited to solving or completing student assignments. Please clarify what you have done yourself and what did not work out. - Yura Ivanov
  • That is the problem - I don’t even know how to approach the solution of the problem. - Pferdeficker
  • divide the task into several. write a request for each part, then connect them with joines. sorting can be achieved by subquery. - Yura Ivanov

2 answers 2

If you do not pay attention to the possible case when the user can leave more than one comment, you get the following query:

  SELECT c.photo_id, p.photo_title, count(c.comment_id) AS comments_counter FROM comments AS c INNER JOIN photos AS p ON p.photo_id = c.photo_id GROUP BY c.photo_id HAVING comments_counter < 5 ORDER BY comments_counter DESC

Demo on SQLfiddle.com .

As a first step, I advise you to look at the "Search" tab in PhpMyAdmin. Construct a query, see what happened, adjust.

     SELECT p.*, COUNT(c.comment_id) as count_photos, COUNT(DISTINCT c.user_id) as count_users FROM `photos` as p LEFT JOIN comments as c ON (c.photo_id=p.photo_id) GROUP BY p.photo_id HAVING count_users > 5 ORDER BY count_photos DESC