加入具有多个结果的表，然后合并为一列

Question

First of all, i know my question heading is little bit or more confusing so let me try to explain.

I have 3 tables,

1. Companies
2. company_branches
3. cities

so with those tables, i want to select all from companies and join company_branches then join cities.

All WITH JUST ONE SQL QUERY, and i want to end up with a list of companies, and each company having it's branches (array) under a column called branches.

See examples bellow.

companies_table

  id    |   name
--------+-----------------------
  1     |  microsoft
--------+-----------------------
  2     |  facebook



company_branches_table

  id    |   company_id    | city_id
--------+-----------------+-------------
  1     |  1              | 3
--------+-----------------+-------------
  2     |  1              | 2
--------+-----------------+-------------
  3     |  2              | 1


cities

  id    |   name     
--------+-------------
  1     |  LA              
--------+-------------
  2     |  New york
--------+-------------
  3     |  Chicago

Here is how i wanted my results to look like

in Json or associative array

[
   {
     id       : 1,
     name     : microsoft,
     branches : [
                  {
                     id    : 2,
                     city_name : New york
                  },   
                  {
                     id    : 3,
                     city_name : Chicago
                  }
                ]
   },
   {
     id       : 2,
     name     : facebook,
     branches : [
                  {
                     id    : 1,
                     city_name : LA
                  }   

                ]
   }

]

I hope u'll understand. thanks in advance

---

[Edit]

---

Listed tables at the top

Answer 1

Maybe this will help

SELECT ct.id,ct.name,group_concat(c.id, '->', c.name) AS branches FROM companies_table AS ct JOIN company_branches_table AS cbt ON ct.id = cbt.company_id JOIN cities AS c ON c.id = cbt.city_id GROUP BY ct.id