title: MYSQL Complex Join one-to-many
body: <p>I have the following tables:</p>
  
  <p><strong>clients:</strong><br/></p>
  
  <pre>
  | id | name     | code | zone  |
  --------------------------------
  | 1  | client 1 | a1b1  | zone1|
  | 2  | client 2 | a2b2  | zone2|
  </pre>
  
  <p><strong>contacts:</strong><br/></p>
  
  <pre>
  | id_contact | first_name  | last_name |
  ----------------------------------------
  | 11         | first1      | last1     |
  | 22         | first2      | last2     |
  | 33         | first3      | last3     | 
  | 44         | first4      | last4     |
  </pre>
  
  <p><strong>client_contacts:</strong><br/></p>
  
  <pre>
  | id_client | id_contact |
  --------------------------
  | 1         | 11         |
  | 1         | 22         |
  | 1         | 33         |
  | 2         | 11         |
  | 2         | 44         |
  </pre>
  
  <p><strong>offers:</strong><br/></p>
  
  <pre>
  | id_offer | id_client | value |
  --------------------------
  | 111      | 1         |   100 |
  | 222      | 1         |   200 |
  | 333      | 1         |   300 |
  | 444      | 2         |   400 |
  </pre>
  
  <p>I would like through a optimal select to obtain:</p>
  
  <pre>
  | id_client | name     | code | zone  | contacts_pers | total_offer_value |
  ----------------------------------------------------------------------------
  | 1         | client 1 | a1b1 | zone1 | first1 last1; | 600               |
                                          first2 last2;
                                          first3 last3;
  | 2         | client 2 | a2b2 | zone2 | first1 last1; | 400               |
                                          first4 last4;
  </pre>
  
  <p>I know how to get the desired result with "group_concat" and stored procedures for "total_offer_value". But how to get the desired result from a single efficient select?</p>