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 | john@email.com | 0123456789 | | Dave | dave@email.com | 0123455789 | | John | john@email.com | 0123456789 | |
| 2 | Company 2 | Sarah | sarah@email.com | 0123466789 | 0723456781 | Sarah | sarah@email.com | 0123466789 | 0723456781 | Sarah | sarah@email.com | 0123466789 | 0723456781 |
| 3 | Company 3 | James | james@email.com | 0123477789 | 0743456788 | Richard | richard@email.com | 0123444789 | 0723444781 | Tom | tom@email.com | 0123433789 | 0723458881 |
+------------+--------------+------------+-----------------+-----------------+------------------+--------------+-------------------+-------------------+--------------------+----------------+-----------------+---------------------+----------------------+
I'm trying to get to:
+------------+--------------+---------+-------------------+------------+------------+-------------------------+
| account_no | company_name | name | email | phone_src | mobile_src | role |
+------------+--------------+---------+-------------------+------------+------------+-------------------------+
| 1 | Company 1 | John | john@email.com | 0123456789 | | Admin,Technical |
| 1 | Company 1 | Dave | dave@email.com | 0123455789 | | Billing |
| 2 | Company 2 | Sarah | sarah@email.com | 0123466789 | 0723456781 | Admin,Billing,Technical |
| 3 | Company 3 | James | james@email.com | 0123477789 | 0743456788 | Admin |
| 3 | Company 3 | Richard | richard@email.com | 0123444789 | 0723444781 | Billing |
| 3 | Company 3 | Tom | tom@email.com | 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.
Best Answer
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