Using group_concat to remove duplicates with linking tables-CodePudding

I am building a library system using MySQL. The database includes tables for the music, composer and arranger. These are linked through linking tables. One piece of music might have multiple composers and/or arrangers. I have a basic query working properly, which shows the title, composer and arranger, but if there are multiple composers or arrangers, they appear on separate lines in the table. I'm trying to use GROUP_CONCAT to combine those rows, with a ; separator (so that each title has only one line in the results, but I'm having trouble incorporating the linking tables into the logic.

Here are samples of my tables:

Music table

music_id	music_title	music_drawer	music_number
5	650 East	37	3

Composer table

composer_id	composer_fullname
319	Oldham, Wayne
167	Hale, Ja

musiccomposerlink table

music_composer_id	music_id	composer_id
1	5	319
2	5	167

Here is my current query:

SELECT
    mus.music_title AS title,
    mus.music_id,
    IFNULL(mus.music_drawer, '') AS drawer,
    IFNULL(mus.music_number, '') AS num,
    IFNULL(com.composer_fullname, '') AS composer

FROM
    music AS mus
    LEFT JOIN musiccomposerlink AS mcl ON mus.music_id = mcl.music_id
    LEFT JOIN composers AS com ON mcl.composer_id = com.composer_id
    
WHERE
    mus.music_title LIKE 'e0%'
ORDER BY
    title,
    composer

This produces the following table of results:

Search Results

Title	music_id	drawer	num	composer
650 East	5	37	3	Hale, Ja
650 East	5	37	3	Oldham, Wayne

What I'd like to accomplish with the GROUP_CONCAT is output that looks like:

Search Results

Title	music_id	drawer	num	composer
650 East	5	37	3	Hale, Ja; Oldham, Wayne

Eventually, I'd like to add an additional column in the results for arrangers. Thanks for any help!

CodePudding user response：

You can use a GROUP_CONCAT() expression like this, in queries with GROUP BY.

GROUP_CONCAT(DISTINCT com.composer ORDER BY com.composer SEPARATOR '; ') composers

In your query replace your existing ORDER BY clause with this:

GROUP BY mus.music_title, 
         mus.music_id,
         IFNULL(mus.music_drawer, ''),
         IFNULL(mus.music_number, '')
ORDER BY mus.music_title

and you'll have the result you want.

Or, if you want to preserve the order of composers in your musiccomposerlink table, use this.

GROUP_CONCAT(com.composer ORDER BY mcl.music_composer_id SEPARATOR '; ') composers

Most librarians insist upon preserving the order of the names of creators of the works they catalog.