I have inherited a table with information about some groups of people in which one field which contains delimited data, with the results matched to another table.
id_group Name
-----------------------
1 2|4|5
2 3|4|6
3 1|2
And in another table I have a list of people who may belong to one or more groups
id_names Names
-----------------------
1 Jack
2 Joe
3 Fred
4 Mary
5 Bill
I would like to perform a select on the group data which results in a single field containing a comma or space delimited list of names such as this from the first group row above "Joe Fred Bill"
I have looked at using a function to split the delimited string, and also looked at sub queries, but concatenating the results of sub queries quickly becomes huge.
Thanks!
As implied by Strawberry's comment above, there is a way to do this, but it's so ugly. It's like finishing your expensive kitchen remodel using duct tape. You should feel resentment toward the person who designed the database this way.
SELECT g.id_group, GROUP_CONCAT(n.Names SEPARATOR ' ') AS Names
FROM groups AS g JOIN names AS n
ON FIND_IN_SET(n.id_names, REPLACE(g.Name, '|', ','))
GROUP BY g.id_group;
Output, tested on MySQL 5.6:
+----------+---------------+
| id_group | Names |
+----------+---------------+
| 1 | Joe Mary Bill |
| 2 | Fred Mary |
| 3 | Jack Joe |
+----------+---------------+
The complexity of this query, and the fact that it will be forced to do a table-scan and cannot be optimized, should convince you of what is wrong with storing a list of id's in a delimited string.
The better solution is to create a third table, in which you store each individual member of the group on a row by itself. That is, multiple rows per group.
CREATE TABLE group_name (
id_group INT NOT NULL,
id_name INT NOT NULL,
PRIMARY KEY (id_group, id_name)
);
Then you can query in a simpler way, and you have an opportunity to create indexes to make the query very fast.
SELECT id_group, GROUP_CONCAT(names SEPARATOR ' ') AS names
FROM groups
JOIN group_name USING (id_group)
JOIN names USING (id_name)