In situation, when you do the data report by Select or Joining the data from one-to-many relation tables in MySQL database. You may found data duplication each of row, and you want to grouping it and concatenate different values into one rows. In objective to normalize the results as we see an example in the cover image of this article.

 

In the actual situation

You have designed two table for your transaction like these.

Player

IdName
1Eriksen
2Young
3Lukaku
4Alexis

Skills

IdSkill
1Dribbling
2Defending
3Shooting
4Passing

 

And you have the relation table for keep the record which represent for one-to-many between table “Player” and “Skills” as follow;

PlayerSkills

IdPlayerIdSkillsId
111
214
322
424
533
641
743
844

 

One day. Your boss ask you as he would like to know each skills of players. So, you just select the data in table by joining like this;

select p.Name, s.Skill
from Player p
left join PlayerSkills ps on p.Id = ps.PlayerId
inner join Skills s on ps.SkillsId = s.Skill

The report are showing below, with row duplication.

NameSkill
EriksenDribbling
EriksenPassing
YoungDefending
YoungPassing
LukakuShooting
AlexisDribbling
AlexisPassing
AlexisShooting

 

You can use a function in MySQL using GROUP_CONCAT to concatenate values of “skills” and separate by comma (,) following as;

select p.Name, GROUP_CONCAT(s.Skill)
from Player p
left join PlayerSkills ps on p.Id = ps.PlayerId
inner join Skills s on ps.SkillsId = s.Skill
group by p.Name

The incredible results are showing as table below, and your boss are so happy ^^

NameSkill
EriksenDribbling, Passing
YoungDefending, Passing
LukakuShooting
AlexisDribbling, Passing, Shooting

 

Remark: GROUP_CONCAT will be useful if others column are duplication.

2 Give us some moral support.
(Visited 46 times, 1 visits today)

2 Comments

  1. Briancoins March 16, 2020 at 5:58 AM

    I love looking at your internet site. Thanks a lot!

    Reply
  2. Dan December 18, 2020 at 1:31 AM

    it work!

    Reply

Leave A Comment

Your email address will not be published.