MySQL select delimited data

Question

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!

Answer 1

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)

Answer 2

Shadow is correct. Your primary problem is the bad design of relations in the database. Typically one designs this kind of business problems as a so-called M:N relation (M to N). To accomplish that you need 3 tables:

• first table is groups that has a GroupId field with primary key on it and a readable name field (e.g. 'group1' or whatever)

• second table is people that looks exactly as you showed above. (do not forget to include a primary key in the PeopleId field also here)

• third table is a bridge table called GroupMemberships. That one has 2 fields GroupId and PeopleId. This table connects the first two with each other and marks the M:N relation. One group can have 1 to N members and people can be members of 1 to M groups.

Finally, just join together the tables in the select and aggregate:

SELECT 
    g.Name,
    GROUP_CONCAT(p.Name ORDER BY p.PeopleId DESC SEPARATOR ';') AS Members
FROM
    Groups AS g
    INNER JOIN GroupMemberships AS gm ON g.GroupId = gm.GroupId
    INNER JOIN people AS p ON gm.PeopleId = p.PeopleId
GROUP BY g.Name;

Answer 3

DROP TABLE IF EXISTS terrible;

CREATE TABLE terrible
(id_group SERIAL PRIMARY KEY
,Name VARCHAR(255) NOT NULL
);

INSERT INTO terrible VALUES
(1,'2|4|5'),
(2,'3|4|6'),
(3,'1|2');


DROP TABLE IF EXISTS names;

CREATE TABLE names
(id_names SERIAL PRIMARY KEY
,names VARCHAR(12) NOT NULL UNIQUE
);

INSERT INTO names VALUES
(1,'Jack'),
(2,'Joe'),
(3,'Fred'),
(4,'Mary'),
(5,'Bill');

SELECT GROUP_CONCAT(y.names ORDER BY FIND_IN_SET(y.id_names,REPLACE(x.name,'|',','))) n 
  FROM terrible x 
  JOIN names y 
    ON FIND_IN_SET(y.id_names,REPLACE(x.name,'|',',')) > 0 
 GROUP 
    BY id_group;
+---------------+
| n             |
+---------------+
| Joe,Mary,Bill |
| Fred,Mary     |
| Jack,Joe      |
+---------------+
3 rows in set (0.00 sec)