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.
Data:
dataID (primary key),
other data,
other data,
other data
Codes:
codeID,
dataID (foreign key),
codeValue
Expected Result: dataID, codeValue1, codeValue2, codeValue3
+3
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);
+1
source to share