SQL JOIN - comments with reactions (2 tables)

Question

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:

ID	Comm_text
1	Example text
2	Another example

and Reactions:

ID	comm_id	usr	etc..
1	1	Peter	another
2	1	John	collon
3	1	Dog	cuz
4	2	Cat	why not

I need to get this:

ID	Comm_text	Reactions_Count	Users
1	Example text	3	Peter, John, Dog
2	Another example	1	Cat

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.

Answer 1

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`