2013-11-17 16:32

使用INNER JOIN显示多个结果


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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答


  • dragam0217 dragam0217 8年前

    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;
    点赞 评论 复制链接分享
  • dqt66847 dqt66847 8年前

    Yes, you could. Although in my opinion the approach you described seems more natural to me. (Having one query/cursor for the companies, and another one for the services)

    You could use one single query to prepare following list:
    Google, Search
    Google, Calendar
    Google, Mail
    Google, Maps
    Yahoo, Search
    Yahoo, Mail
    (I have left out the ids for simplicity)
    Then you run through the list, every time the company changes you begin a new result line. I find this a bit more complicated than the approach you described.

    点赞 评论 复制链接分享