-1

I have the following tables:

clients:

| id | name     | code | zone  |
--------------------------------
| 1  | client 1 | a1b1  | zone1|
| 2  | client 2 | a2b2  | zone2|

contacts:

| id_contact | first_name  | last_name |
----------------------------------------
| 11         | first1      | last1     |
| 22         | first2      | last2     |
| 33         | first3      | last3     | 
| 44         | first4      | last4     |

client_contacts:

| id_client | id_contact |
--------------------------
| 1         | 11         |
| 1         | 22         |
| 1         | 33         |
| 2         | 11         |
| 2         | 44         |

offers:

| id_offer | id_client | value |
--------------------------
| 111      | 1         |   100 |
| 222      | 1         |   200 |
| 333      | 1         |   300 |
| 444      | 2         |   400 |

I would like through a optimal select to obtain:

| 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;

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?

billssam
  • 1
  • 1

1 Answers1

0

SELECT c.id, c.name, c.code, c.zone, GROUP_CONCAT(DISTINCT CONCAT(co.first_name, " ", c.last_name) SEPARATOR ";") AS contact_pers, func_total_offer_value(c.id) AS total_offer_value

FROM clients c

LEFT OUTER JOIN (client_contacts cc, contacts co) ON ( c.id = cc.id_client AND cc.id_contact = co.id_contact ) GROUP BY c.id

billssam
  • 1
  • 1