MySQL concat with an INNER JOIN

Question

I have 2 tables: manuscript and manuscript_log.

I would like to display for each manuscript the corresponding logs on the same line.

Example: I have two manuscripts 1 and 2. The manuscript 1 has 2 logs and the manuscript 2 has 3 logs.

I would like to get two results in the query, grouped by the manuscrit id:

manuscript_id     manuscript_log
1.                      1,2
2.                     3,4,5

SELECT manuscript.id, manuscript_log.log_number
FROM manuscript INNER JOIN manuscript_log
              ON manuscript.id = manuscript_log.manuscript_id

Answer 1

You can use the GROUP_CONCAT aggregate function

SELECT manuscript.id, GROUP_CONCAT(manuscript_log.log_number)
FROM manuscript INNER JOIN manuscript_log
              ON manuscript.id = manuscript_log.manuscript_id
GROUP BY manuscript.id