CODEWITHSUNDEEP
mysqlsql
Rene
Rene

Reputation: 13567

I would like to concatenate cells in one column

I would like to concatenate cells in one column based on the id and the employee that they belong too.

my tables are:

evenement:

id  |  title
1   |  hello
2   |  hi
3   |  bye

evenementontvanger:

id  |  idEvent  |  idWerknemer
1   |     1     |      2
2   |     1     |      20
3   |     2     |      3
4   |     2     |      18
5   |     3     |      2
6   |     3     |      20
7   |     3     |      26


Werknemer:
idWerknemer   |   Initialen
2             |      JD
3             |      HA
18            |      AD
20            |      RC
26            |      AJ

I would like to have an output like this for each different idEvent:
id  |  title   |   idEvent   |   idWerknemer   |   idWerknemer   |   Initialen  |   Initials
1   |  hello   |   1         |   2             |   2             |   JD         |   JD,RC
1   |  hello   |   1         |   20            |   20            |   RC         |   JD,RC

i've tried to use CONCAT_GROUP but that concatenates all initialen at once.

  SELECT * 
FROM `evenement` LEFT JOIN `evenementontvanger` 
 ON `evenementontvanger`.`idEvent` = `evenement`.`id` 
 LEFT JOIN `Werknemer` ON `Werknemer`.`Initialen`= `evenementontvanger`.`idWerknemer`
 LEFT JOIN (SELECT `Initialen`,`idWerknemer`,
                  GROUP_CONCAT(`Initialen`) AS `Initials`
            FROM `Werknemer` ) a 
 ON a.`idWerknemer` = `evenementontvanger`.`idWerknemer`

 WHERE `idEvent`=`evenement`.`id` 
AND `evenementontvanger`.`idWerknemer` =a.`idWerknemer`

Upvotes: 1

Views: 51

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You can use a co-related subquery it will show all the related item to event in a comma seperated list with duplicate listing of events as shown in your desired result set 

SELECT * 
,
(SELECT GROUP_CONCAT(ww.`Initialen`) FROM `Werknemer` ww
 JOIN evenementontvanger evv
 ON `ww`.`idWerknemer`= `evv`.`idWerknemer`
 WHERE `evv`.`idEvent`  = `e`.`id`
) INITIALENs
FROM `evenement`  e
LEFT JOIN `evenementontvanger`  ev
 ON `ev`.`idEvent` = `e`.`id` 
 LEFT JOIN `Werknemer` w 
ON `w`.`idWerknemer`= `ev`.`idWerknemer`

Fiddle

If you need to show for distinct event you can simply do so

SELECT * 
,
(SELECT GROUP_CONCAT(`w`.`Initialen`))
 INITIALENs
FROM `evenement`  e
LEFT JOIN `evenementontvanger`  ev
 ON `ev`.`idEvent` = `e`.`id` 
 LEFT JOIN `Werknemer` w 
ON `w`.`idWerknemer`= `ev`.`idWerknemer` GROUP BY e.id

Fiddle 2

Upvotes: 1

Related Questions