SELECT * FROM comments LEFT JOIN user ON user_id = users LEFT JOIN news ON user = users WHERE data_id =9

As in the same request, count how many comments.id for each user.user_id if user.user_id == comments.users
If you don’t understand the question, don’t poke it - just ask for clarification.

  • The user is not needed at all in the request, if there is only an ID that you take from the comments. It is also not clear why there is even more glueing and some news that can affect the final count in the records. those. select users, count(1) from comments group by users will already give the number of select users, count(1) from comments group by users for each users - Mike
  • @Mike in this query is all clear, using data from all tables. But there are still not enough comments.id for each user. Not a tragedy of course, but I would like to get this data. In real life it will look like comments to the news, in which for each user his total number of comments on the site will be displayed. - wwwplaton 4:04 pm

1 answer 1

 SELECT * FROM comments C LEFT JOIN user U ON U.user_id = C.users LEFT JOIN news N ON N.user = C.users LEFT JOIN (select users, count(1) as user_com_cnt from comments group by users) CN ON CN.users = C.users WHERE data_id = 9

Unfortunately, this request can be slow, because each time will collect statistics on all comments of all users. It may be more reasonable to periodically calculate such statistics and save this number in a separate field in the users table. Better yet, keep this number in the users table with triggers to add / remove comments in comments.

  • I understand that Every derived table must have its own alias . There is no room for me to run away from it, and will I have to take advice? - wwwplaton
  • This is due to the fact that the columns are the same in the two tables called. In general, it is a good tone to give all tables short aliases and specify all the column names with them. Put as much as I could - Mike
  • A request for 10 comments took `[1] => 0.0040194988250732` Of course, as I grow, I’ll follow this parameter. Thank you very much - wwwplaton
  • correct the typo LEFT JOIN news N ON N.user = C.users system does not allow less than 6 characters to be edited :)) And the answer will be accepted, better as I have nowhere to go. - wwwplaton
  • @wwwplaton ok :) - Mike