Aggregate function GROUP_CONCAT(expr) in MySQL

debugcn Published at Dev

Antonio Mailtraq

I have problem in using the aggregate function GROUP_CONCAT(expr) in MySQL database: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

I have this table with this rows:

+------+------+
| NAME | AREA |
+------+------+
| and  | DM4  |
| ant  | DMS  |
| don  | DM7  |
| ett  | DM4  |
| fab  | DM4  |
| fra  | DMS  |
| gia  | DM7  |
| gug  | DM2  |
| raf  | DM   |
| rob  | DM2  |
| vin  | DM7  |
+------+------+
16 rows in set

And I tried aggregate query to obtain this output where the name raf should be present in all areas because your area is DM:

+------+------------------------------+
| AREA | group_emails                 |
+------+------------------------------+
| DM4  | and; ant; ett; fab; fra; raf |
| DM6  | and; ant; raf                |
| DM7  | and; don; gia; vin; raf      |
| DM2  | gug; rob; raf                |
| DMS  | ant; fra ; raf               |
+------+------------------------------+

I have tested this solution but the output is wrong.

This is beginning to make me believe my structure as a whole is not correct.

What am I missing ?

I would greatly appreciate any help you can give me in working this problem.

mysql> SELECT
    AREA,
    GROUP_CONCAT(
        DISTINCT NAME
        ORDER BY
            NAME ASC SEPARATOR '; '
    ) AS group_emails
FROM
    `tbl_NAMES`
GROUP BY
        AREA
ORDER BY
    NAME ASC;
+------+-------------------------+
| AREA | group_emails            |
+------+-------------------------+
| DM4  | and; ant; ett; fab; fra |
| DM   | raf                     |
| DM6  | and; ant                |
| DM7  | and; don; gia; vin      |
| DM2  | gug; rob                |
| DMS  | ant; fra                |
+------+-------------------------+
6 rows in set

Bohemian

You need to base your data on a union of a straight select with a join to itself using a LIKE comparison to collect all the names that "match":

SELECT
    AREA,
    GROUP_CONCAT(
        DISTINCT NAME
        ORDER BY NAME
        SEPARATOR '; ') AS group_emails
FROM (
SELECT * FROM tbl_NAMES
UNION
SELECT t2.name, t1.AREA
FROM tbl_NAMES t1
LEFT JOIN tbl_NAMES t2
  ON t1.AREA like concat(t2.AREA, '%')
) x
GROUP BY AREA
ORDER BY group_emails

See SQLFiddle

Collected from the Internet

Please contact [email protected] to delete if infringement.