CODEWITHSUNDEEP
mysqlsqlgroup-concat
Yanis Boucherit
Yanis Boucherit

Reputation: 714

Concat result from join

I have the following (little bit long) query :

 SELECT * 
        FROM client C
        JOIN uga U
        ON U.id_uga = C.id_uga
        JOIN appartenance A
        ON A.id_uga = U.id_uga
        JOIN serviceattribuee SA
        ON SA.id_client = C.id_client
        JOIN service S
        ON S.id_service = SA.id_service
        WHERE A.id_utilisateur = 28
        ORDER BY ville_client

Which returns me something like :

Nom : "Test" 
Adresse : "Test"
Services : "Service 1"

Nom : "Test" 
Adresse : "Test"
Services : "Service 2"


Nom : "Test 2"
Adresse : "Test 2"
Services : "Service 1"

Nom : "Test 2"
Adresse : "Test 2"
Services : "Service 2"

The thing is, as you can see, the results are duplicated, because there's several services for each client. Is there a way to concat all the services into the same row for each client, in order to avoid doubles ?

I'm using MySQL5.5

Thanks !

Upvotes: 0

Views: 117

Answers (3)

Chris Trahey
Chris Trahey

Reputation: 18290

I think what you are looking for is GROUP_CONCAT with a GROUP BY clause. In order to use it well, you will need to specifically enumerate the columns you want to concat (I've shown here a sample, you can adapt to your needs:

SELECT 
  C.*
  GROUP_CONCAT(S.Nom) as `services`
FROM client C
  JOIN uga U
  ON U.id_uga = C.id_uga
  JOIN appartenance A
  ON A.id_uga = U.id_uga
  JOIN serviceattribuee SA
  ON SA.id_client = C.id_client
  JOIN service S
  ON S.id_service = SA.id_service
WHERE A.id_utilisateur = 28
GROUP BY C.id
ORDER BY ville_client

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269673

You want to group things and use group_concat:

select nom, addresse, ville_client,
       group_concat(services separator ', ')
FROM client C JOIN
     uga U
     ON U.id_uga = C.id_uga JOIN
     appartenance A
     ON A.id_uga = U.id_uga JOIN
     serviceattribuee SA
     ON SA.id_client = C.id_client JOIN
     service S
     ON S.id_service = SA.id_service
group by nom, addresse, ville_client
WHERE A.id_utilisateur = 28
ORDER BY ville_client

Upvotes: 2

Adriano Carneiro
Adriano Carneiro

Reputation: 58595

    SELECT c.Nom, c.Adresse, group_Concat(s.Nom) as services
    FROM client C
    JOIN uga U
    ON U.id_uga = C.id_uga
    JOIN appartenance A
    ON A.id_uga = U.id_uga
    JOIN serviceattribuee SA
    ON SA.id_client = C.id_client
    JOIN service S
    ON S.id_service = SA.id_service
    WHERE A.id_utilisateur = 28
    group by c.Nom, c.Adresse

Upvotes: 3

Related Questions