使用INNER JOIN显示多个结果

Question

I have these three MySQL tables:

companies                    services                      auxiliary
id_company name_company      id_service  name_service      id_company id_service   

1          Google            1           Search            1          1   
2          Yahoo             2           Calendar          1          2  
                             3           Mail              1          3
                             4           Maps              1          4
                                                           2          1
                                                           2          3

I am wondering if I could display with only one query (I'm using php):

Google: Search, Calendar, Mail, Maps.
Yahoo: Search, Mail.

Now what I am doing is display the companies, and for each company I make another MySQL query to display the services. Is it possible to do that with only one query? Thank you.

Answer 1

The answer to your question is: "Yes".

Oh, you want to know how to do it as well. The key is to join the tables together and then aggregate at the company level. I am going to assume that you really want the output as two columns (company name, services), rather than one string with them concatenated together.

The SQL is:

select c.name_company, group_concat(s.name_service separator ', ') as services
from auxiliary a join
     companies c
     on a.id_company = c.id_company join
     services s
     on a.id_service =  s.id_service
group by c.id_company, c.name_company;