CODEWITHSUNDEEP
mysqlinner-joinconcatenation
Milos Cuculovic
Milos Cuculovic

Reputation: 20223

MySQL concat with an INNER JOIN

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

Upvotes: 6

Views: 14304

Answers (1)

gbn
gbn

Reputation: 432261

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

Upvotes: 8

Related Questions