I need to make one SQL command. From table with comments i'll get comment id, then with this ID I need to get count of reactions with the same comment ID and user's names.

So for example I have this 2 tables:

Comments:

and Reactions:

I need to get this:

I tried this:

SELECT k.id, k.comm, COUNT(r.id) as reactions, r.usr
FROM `comms` k
  INNER JOIN `reactions` r ON r.id=k.id

It's just one row with one comment and count of all rows in reaction table.

Thanks.

CodePudding user response：

Try this query that makes the same output:

select comments.id as ID , comments.Comm_text as Comm_text , 
(select count(id) from Reactions where comm_id = comments.id) as Reactions_Count ,
(select coalesce(GROUP_CONCAT(usr ORDER BY usr DESC) , '') from Reactions WHERE comm_id = comments.id) as Users

from comments group by comments.id

You should use group by to group the comments and have just one row then use query to count and gather the data, based on each row of the group.

The GROUP_CONCAT attach the output with , and the coalesce set the output to a given string if the output was empty.

Read more about:

• GROUP BY
• GROUP_CONCAT
• COALESCE
• subquery

CodePudding user response：

According to the names that u set in the example, this will work. Just fix the table names for your database structure.

SELECT `Comments`.`ID`, `Comments`.`Comm_text`, count(`Reactions`.`comm_id`) as react, `Reactions`.`usr`
FROM `Comments`
INNER JOIN `Reactions`
ON `Comments`.`ID`=`Reactions`.`comm_id`
GROUP BY `Reactions`.`comm_id`