Solved

GROUP_CONCAT

Posted on 2010-11-08
2
771 Views
Last Modified: 2012-05-10
I'm trying to return all rows from a Business table (where RegionID is 1) while joining all phone numbers for that Business from the BusinessPhoneNumber table into a concatenated string, in a new column (say PhoneNumbers). My understanding is this can be done with GROUP_CONCAT() - here is my current SQL:

SELECT * FROM `Business`
LEFT JOIN `BusinessPhoneNumber` ON `BusinessPhoneNumber`.`ParentID` = `Business`.`ID`
WHERE `Business`.`RegionID` = 1

Please provide an example that uses GROUP_CONCAT to place the BusinessPhoneNumber's in a comma separated columns. FYI - the phone number field in that table is simply Phone (BusinessPhoneNumber.Phone).
0
Comment
Question by:level9wizard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34087343
SELECT B.*, P.PhoneList
FROM Business B
LEFT JOIN
(
SELECT Business.ID, group_concat(BusinessPhoneNumber.Phone) PhoneList
FROM Business
INNER JOIN BusinessPhoneNumber ON BusinessPhoneNumber.ParentID = Business.ID
WHERE Business.RegionID = 1
) P ON P.ID = B.ID
WHERE B.RegionID = 1
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34087349
Sorry left out a group by

SELECT B.*, P.PhoneList
FROM Business B
LEFT JOIN
(
SELECT Business.ID, group_concat(BusinessPhoneNumber.Phone) PhoneList
FROM Business
INNER JOIN BusinessPhoneNumber ON BusinessPhoneNumber.ParentID = Business.ID
WHERE Business.RegionID = 1
GROUP BY Business.ID
) P ON P.ID = B.ID
WHERE B.RegionID = 1
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question