CODEWITHSUNDEEP
mysqlgroup-bygroup-concat
Benson
Benson

Reputation: 21

How to avoid group_concat separator duplicate in mysql select result

I have 2 tables call record and user, their fields are below

record fields:

  • recordID (key)
  • userID (key)

user fields:

  • userID (key)
  • userName
  • userNote

and I want to know recordID, userName, userNote group by recordID Here is my sql

 select record.recordID, group_concat(user.userName) as USERNAMES , group_concat(user.userNote) as USERNOTES from record 
 left outer join user on record.userID = user.USERID  
 group by record.recordID

now I can split USERNAMES and USERNOTES with ',' to know every username and his usernote

But If there is a ',' in userNote ,It might make mistakes

for example, BEN userNote is nice,tall and TOM's userNote is nice

the sql result will be

USERNAMES-> BEN,TOM

USERNOTES-> nice,tall,nice

In this case USERNOTES will have 3 notes, I can't differ from which one is TOM'S note

Is there any way to avoid this situation?

Or the best way I can do is use a separator that is hard to appear, like '！＠＃＠！'

In my real case, I have 10 tables related to a key, so if I left outer join all tables, it will be tons of duplicate rows,

Upvotes: 2

Views: 222

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

One method is a different separator:

select r.recordID,
       group_concat(u.userName separator ';') as USERNAMES ,   
       group_concat(u.userNote separator ';') as USERNOTES
from record r left outer join
     user u
     on r.userID = u.USERID  
group by r.recordID ;

Upvotes: 2

Related Questions