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

See http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field for a hugely more upvoted question/answer on the identical issue. — Dan Nissenbaum, Nov 24 '15 at 05:06

score 9 · Accepted Answer · answered May 27 '13 at 13:33

9

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

answered May 27 '13 at 13:33

gbn

422,506
82
585
676

2

i suggest a modification to use `group_concat(distinct manuscript_log.log_number order by manuscript_log.log_number separator ',')` – gillyspy May 27 '13 at 13:36
Playing around a bit with this i found out you can also concat multiple fields inside the group_concat like this `GROUP_CONCAT(DISTINCT manuscript_log.log_number, ' ', manuscript_log.some_other_key_eg_email separator '; ')` only thing though is that the distinct field is always first... hope that helps! ;) – MediaVince Dec 04 '14 at 11:26

MySQL concat with an INNER JOIN

1 Answers1