MySQL, get joined records separated by commas
Posted: Thu Nov 20, 2008 3:05 am
Hi everyone
I'm converting our database system from Filemaker to MySQL and things are going well.
However I've got quite a specific problem that I need to solve now.
There's 3 tables in my database:
companies - stores contact details of our suppliers. One record per company.
classifications - all the available classifications that a company can have. Manufacturer, Distributor, Event Organiser etc. Has 2 fields, classification_id and classification_name
classifications_data - stores which classifications are selected for which company. Only has 2 fields: company_id and classification_id and basically links the companies and classifications databases together
This is all fine and it works well. It allows multiple classifications to be stored for each company, and it's a the sort of standard normalised DB structure I've been looking to achieve for some years.
I hope I've explained it clearly.
But i'm looking to build a query which returns 2 columns: a company_name field from the companies table, and another column which is a comma-separated list of the classification names that each company has from classifications_data.
So something like this:
It's the comma separated list of the related classifications I'm having trouble with.
Is that possible directly within MySQL?
The alternative is to do a LEFT/RIGHT JOIN and loop through the related records in PHP, building the comma-separated list for that company then moving on to the next company. But that's a bit of a horrible old-school way of doing things.
Just wondering if there are any options to do this within the MySQL query?
Rather than manually restructuring the data with PHP.
Thanks, Ben
I'm converting our database system from Filemaker to MySQL and things are going well.
However I've got quite a specific problem that I need to solve now.
There's 3 tables in my database:
companies - stores contact details of our suppliers. One record per company.
classifications - all the available classifications that a company can have. Manufacturer, Distributor, Event Organiser etc. Has 2 fields, classification_id and classification_name
classifications_data - stores which classifications are selected for which company. Only has 2 fields: company_id and classification_id and basically links the companies and classifications databases together
This is all fine and it works well. It allows multiple classifications to be stored for each company, and it's a the sort of standard normalised DB structure I've been looking to achieve for some years.
I hope I've explained it clearly.
But i'm looking to build a query which returns 2 columns: a company_name field from the companies table, and another column which is a comma-separated list of the classification names that each company has from classifications_data.
So something like this:
Code: Select all
ABC Widget Co MANUFACTURER,DISTRIBUTOR
London Carrier Co DISTRIBUTOR
WXY Gadgets Ltd MANUFACTURER,EVENT ORGANISERIs that possible directly within MySQL?
The alternative is to do a LEFT/RIGHT JOIN and loop through the related records in PHP, building the comma-separated list for that company then moving on to the next company. But that's a bit of a horrible old-school way of doing things.
Just wondering if there are any options to do this within the MySQL query?
Rather than manually restructuring the data with PHP.
Thanks, Ben
