php - combine three tables and count a specific value with mysql -

i have following mysql table structure:

table 'company'         table 'serial'                   table 'product' +----+--------------+   +-------------------+--------+   +-----+---------+ | id | name         |   | serial            |cid     |   | nr  | name    | +----+--------------+   +-------------------+--------+   +-----+---------+ |  1 | 1 inc.     |   | 100a000001        |      1 |   | 100 | phone   | |  2 | 2 corp.    |   | 100a000002        |      1 |   | 101 | ball    | |  3 | 3 corp.  |   | 102a000003        |      1 |   | 102 | egg     | |  4 | 4 inc.    |   | 103a000004        |      2 |   | 103 | box     | |  5 | 5 inc.    |   | 103a000005        |      3 |   | 104 | cable   | +----+--------------+   | 101a000006        |      4 |   +-----+---------+                         | 102a000007        |      4 |                         | 103a000011        |      5 |                         | 103a000010        |      5 |                         | 104a000007        |      5 |                         | 104a000008        |      5 |                         | 103a000009        |      5 |                         +-------------------+--------+ 

a short description how work together:

1. cid in table 'serial' company id table 'company'
2. nr in table 'product' defines how first 3 digits of serial have desired product.

what need output this:

+-------------+--------------------------------------+ | customer    | products                             | +-------------+--------------------------------------+ | 1 inc.    | phone (2), egg (1)                   | +-------------+--------------------------------------+ | 2 corp.   | box (1)                              | +-------------+--------------------------------------+ | 3 corp. | box (1)                              | +-------------+--------------------------------------+ | 4 inc.   | phone (1), ball (1)                  | +-------------+--------------------------------------+ | 5 inc.   | box (3), cable (2)                   | +-------------+--------------------------------------+ 

but don't know how write query!

my solution @ moment following query:

select     c.name,     s.serial     company c inner join     serial s on     c.id = s.cid order     c.name desc 

this generates output this:

+-------------+--------------+  | customer    | serial       | +-------------+--------------+  | 1 inc.    | 100a000001   | | 1 inc.    | 100a000002   | | 1 inc.    | 102a000003   | | 2 corp.   | 103a000004   | | 3 corp. | 103a000005   | | 4 inc.   | 101a000006   | | 4 inc.   | 102a000007   | | 5 inc.   | 103a000011   | | 5 inc.   | 103a000010   | | 5 inc.   | 104a000007   | | 5 inc.   | 104a000008   | | 5 inc.   | 103a000009   | +-------------+--------------+ 

at moment i'm using output , php i'm generating desired output, great without php.

so i'm asking is: how can solve nice mysql query, output described above?

select customer, group_concat(concat(pname, ' (', pcount, ')')) products (     select         c.name customer,         p.name pname,         count(*) pcount             company c     inner join         serial s     on         c.id = s.cid     inner join         product p     on p.nr = left(s.serial, 3)     group customer, pname) x group customer order     customer desc