Changing mysql query to combine multiple columns instead of of seperate lines

Question

I have 2 tables like this:

Table: Company
id| Name         | Area
1 | Company A    | New York
2 | Company B    | New York
3 | Company C    | Chicago

Table: Service
id| Type
1 | Service
1 | Delivery
2 | Hotel
2 | Restaurants
3 | Movers

I would like to get results just for New York like this (without chicago):

Name      | Area    | Type
Company A | New York| Service, Delivery
Company B | New York| Hotel, Restaurants

But i instead get this:

Name      | Area     | type
Company A | New York | Service
Company A | New York | Delivery
Company B | New York | Hotel
Company B | New York | Restaurants

I have tried using distinct and concat to solve my problems but the queries do not end, they instead time out after running for over 6 seconds. If i run a query to get the results i get right now, i get results in less then 1s.

Here are the queries i have tried so far:

select p1.Name,
GROUP_CONCAT(p2.Type) from company as p1, service as p2
GROUP BY p1.CompanyName
LIMIT 10;

Result: Time out

select DISTINCT company.Name from company
LEFT JOIN service
ON company.Number = service.Number AND service.Type LIKE '% York'
Limit 50;

Result: Never Ends (not even a time out)

select p1.Name, p2.type from company as p1, service as p2
where p1.id = p2.id
LIMIT 10;

Result: Shows really messed up results which are not possible like this:

Name      | Type
Company A | Hotel
Company B | Delivery
Company B | Restaurants

The DB was originally Microsoft Access and i was getting such results in there, not being familiar with microsoft access i figured microsoft must be having problems and tried to get the query executed in mysql, but the same problems happening there... if i try to manually tally the data whats being shown does not tally at all and this has me really confused... can somebody please look into my queries and advise me what am i doing wrong? thx!

this works with Code-Monks help, heres a modified query which handles partial searches: select c.name,c.area,group_concat(s.Type) as type from Company c inner join service s on c.id=s.id where c.area LIKE '% York' group by s.id; — somanit, Aug 31 '15 at 05:27

Abhishek Ginani · Accepted Answer · 2015-08-29T12:24:53.080

0

use inner join to get matched records and group results by company id.

Here is the query:

 select c.name,c.area,group_concat(s.Type) as type 
 from Company c inner join service s on c.id=s.id
 where c.area='New York'
 group by s.id;

edited Aug 29 '15 at 12:24

answered Aug 29 '15 at 12:18

Abhishek Ginani

4,511
4
23
35

thanks for your reply monk, the query seems to run pretty fast, giving responses well! im sorry i cant upvote your answer i dont have enough points yet... – somanit Aug 31 '15 at 05:26

score 0 · Answer 2 · answered Aug 29 '15 at 12:18

0

Try this:

SELECT c.Name, c.Area, GROUP_CONCAT(s.Type SEPARATOR ', ') AS TYPE
FROM Company AS c 
LEFT JOIN Service AS s ON c.id = s.id 
WHERE c.Area = 'New York'
GROUP BY c.id;

answered Aug 29 '15 at 12:18

Saharsh Shah

28,687
8
48
83

thanks for your reply saharsh, unfortunately the query timesout with Error Code: 2013. Lost connection to MySQL server during query after running for 600.589 sec – somanit Aug 31 '15 at 05:20

Changing mysql query to combine multiple columns instead of of seperate lines

2 Answers2