MySQL concatenates one-many into one line

I have one or two customization tables. What I want to do is join the tables, but instead of returning multiple rows showing different combinations, I want it to return 1 row that has 3 fields, one for each row from many tables.

dataID (primary key),
other data,
other data,
other data

dataID (foreign key),


Expected Result: dataID, codeValue1, codeValue2, codeValue3


source to share

1 answer

select concat(DataID,',',CodeValues) ExpectedOutput from
    select A.dataID,GROUP_CONCAT(B.codeValue ORDER BY B.codeID) CodeValues
    from (select dataID from Data) A
    inner join Codes B using (dataID) group by A.dataID
) AA;


Make sure you have this composite index

ALTER TABLE Codes ADD INDEX data_code_ndx (dataID,codeID,codeValue);




All Articles