SELECT multiple columns when using GROUP_CONCAT with UNION

Question

I have a frustrating situation caused by poor database design - unfortunately that part is out of my control.

This is the table definition:

CREATE TABLE `account` (
 `account_no` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `company_name` varchar(120) NOT NULL DEFAULT '',
 `admin_name` varchar(120) NOT NULL DEFAULT '',
 `admin_email` varchar(120) DEFAULT '',
 `admin_phone_src` varchar(33) DEFAULT NULL,
 `admin_mobile_src` varchar(33) DEFAULT NULL,
 `billing_name` varchar(120) NOT NULL DEFAULT '',
 `billing_email` varchar(120) DEFAULT '',
 `billing_phone_src` varchar(33) DEFAULT NULL,
 `billing_mobile_src` varchar(33) DEFAULT NULL,
 `technical_name` varchar(120) NOT NULL DEFAULT '',
 `technical_email` varchar(120) DEFAULT '',
 `technical_phone_src` varchar(33) DEFAULT NULL,
 `technical_mobile_src` varchar(33) DEFAULT NULL
 PRIMARY KEY (`account_no`)
) ENGINE=InnoDB AUTO_INCREMENT=10476 DEFAULT CHARSET=utf8

I need to produce a list of people with the role comma delimited. I.e. admin, billing, technical may all refer to the same person (duplicated data - urgh!), or they might be 3 different people.

Given the dataset:

+------------+--------------+------------+-----------------+-----------------+------------------+--------------+-------------------+-------------------+--------------------+----------------+-----------------+---------------------+----------------------+
| account_no | company_name | admin_name | admin_email | admin_phone_src | admin_mobile_src | billing_name | billing_email | billing_phone_src | billing_mobile_src | technical_name | technical_email | technical_phone_src | technical_mobile_src |
+------------+--------------+------------+-----------------+-----------------+------------------+--------------+-------------------+-------------------+--------------------+----------------+-----------------+---------------------+----------------------+
| 1 | Company 1 | John | [email protected] | 0123456789 | | Dave | [email protected] | 0123455789 | | John | [email protected] | 0123456789 | |
| 2 | Company 2 | Sarah | [email protected] | 0123466789 | 0723456781 | Sarah | [email protected] | 0123466789 | 0723456781 | Sarah | [email protected] | 0123466789 | 0723456781 |
| 3 | Company 3 | James | [email protected] | 0123477789 | 0743456788 | Richard | [email protected] | 0123444789 | 0723444781 | Tom | [email protected] | 0123433789 | 0723458881 |
+------------+--------------+------------+-----------------+-----------------+------------------+--------------+-------------------+-------------------+--------------------+----------------+-----------------+---------------------+----------------------+

I'm trying to get to:

+------------+--------------+---------+-------------------+------------+------------+-------------------------+
| account_no | company_name | name | email | phone_src | mobile_src | role |
+------------+--------------+---------+-------------------+------------+------------+-------------------------+
| 1 | Company 1 | John | [email protected] | 0123456789 | | Admin,Technical |
| 1 | Company 1 | Dave | [email protected] | 0123455789 | | Billing |
| 2 | Company 2 | Sarah | [email protected] | 0123466789 | 0723456781 | Admin,Billing,Technical |
| 3 | Company 3 | James | [email protected] | 0123477789 | 0743456788 | Admin |
| 3 | Company 3 | Richard | [email protected] | 0123444789 | 0723444781 | Billing |
| 3 | Company 3 | Tom | [email protected] | 0123433789 | 0723458881 | Technical |
+------------+--------------+---------+-------------------+------------+------------+-------------------------+

I've managed to use UNION to get the people into rows instead of columns (i.e. impersonating a 'sane' database design), and adding the role like this:

SELECT account_no, company_name, admin_name AS 'Name', admin_email AS 'Email', admin_phone_src AS 'Phone', admin_mobile_src AS 'Mobile', 'Admin' AS role FROM account
UNION
SELECT account_no, company_name, billing_name AS 'Name', billing_email AS 'Email', billing_phone_src AS 'Phone', billing_mobile_src AS 'Mobile', 'Billing' AS role FROM account
UNION
SELECT account_no, company_name, technical_name AS 'Name', technical_email AS 'Email', technical_phone_src AS 'Phone', technical_mobile_src AS 'Mobile', 'Technical' AS role FROM account

I am now trying to merge those rows to get DISTINCT people and produce a comma separated list of roles. I think that's possible using GROUP_CONCAT but my attempt to do that resulted in just 1 row:

SELECT t.account_no, t.company_name, t.Name, t.Email, t.Phone, t.Mobile, GROUP_CONCAT(t.role) FROM (
 SELECT account_no, company_name, admin_name AS 'Name', admin_email AS 'Email', admin_phone_src AS 'Phone', admin_mobile_src AS 'Mobile', 'Admin' AS role FROM account
 UNION
 SELECT account_no, company_name, billing_name AS 'Name', billing_email AS 'Email', billing_phone_src AS 'Phone', billing_mobile_src AS 'Mobile', 'Billing' AS role FROM account
 UNION
 SELECT account_no, company_name, technical_name AS 'Name', technical_email AS 'Email', technical_phone_src AS 'Phone', technical_mobile_src AS 'Mobile', 'Technical' AS role FROM account
) t

Any help much appreciated.

Answer 1

You need a group by to use the group_concat, GROUP BY t.account_no, t.company_name, t.Name, t.Email, t.Phone, t.Mobile

Anyway, I would do it in two separate tables, you have to much duplicate information on that way

CREATE TABLE `account` (
 `account_no` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `company_name` varchar(120) NOT NULL DEFAULT '',
 `admin_id` int(10) unsigned NOT NULL DEFAULT '',
 `billing_id` int(10) unsigned NOT NULL DEFAULT '',
 `technical_id` int(10) unsigned NOT NULL DEFAULT ''
 PRIMARY KEY (`account_no`)
) ENGINE=InnoDB AUTO_INCREMENT=10476 DEFAULT CHARSET=utf8
CREATE TABLE `people` (
 `people_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(120) NOT NULL DEFAULT '',
 `email` varchar(120) DEFAULT '',
 `phone_src` varchar(33) DEFAULT NULL,
 `mobile_src` varchar(33) DEFAULT NULL
 PRIMARY KEY (`people_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10476 DEFAULT CHARSET=utf8

• Damien - Layershift: Yep, I agree the schema sucks - unfortunately I can't change it. GROUP BY was the bit I was missing, thanks