How do I combine multiple rows into one row in mysql

Question

I had a query that joins 4 tables. Tables are user, user_info, email and phone. A user can have one or more phones and emails.

Here is the sql query:

SELECT ur.login_id as loginId, ue.user_email_address as email, up.phone_number as phone
      FROM greydeltadb.gd_user ur 
      LEFT JOIN greydeltadb.gd_user_info uri ON uri.user_id=ur.user_id  
      INNER JOIN greydeltadb.gd_user_emails ue ON ur.user_id = ue.user_id  
      INNER JOIN greydeltadb.gd_phone up  ON ur.user_id = up.user_id  
      ORDER BY ur.login_id

When I execute this query i get result like this for a user with 2 emails and 2 phones:

loginId                  email                        phone
abc                      [email protected]                123456
abc                      [email protected]                798446
abc                      [email protected]                123456
abc                      [email protected]                798446

Then I learnt about GROUP_CONCAT() the updated query is:

SELECT ur.login_id, GROUP_CONCAT(ue.user_email_address) as emails,
      GROUP_CONCAT(up.phone_number) as phones
      FROM greydeltadb.gd_user ur 
      LEFT JOIN greydeltadb.gd_user_info uri ON uri.user_id=ur.user_id  
      INNER JOIN greydeltadb.gd_user_emails ue ON ur.user_id = ue.user_id  
      INNER JOIN greydeltadb.gd_phone up  ON ur.user_id = up.user_id  
      GROUP BY ur.login_id

this query returns the result:

loginId                  email                                                                    phone
abc                      [email protected], [email protected],[email protected], [email protected]                123456, 798446,123456, 798446

But I want the output to be:

loginId                  email                                        phone
abc                      [email protected], [email protected]                123456, 798446

Please help.

add distinct to the inside of group_concat

Himanshu
– Himanshu

2018-12-25 11:11:40 +00:00
Commented Dec 25, 2018 at 11:11 — Himanshu
– Himanshu, Commented Dec 25, 2018 at 11:11

Himanshu · Accepted Answer · 2018-12-25 11:13:12Z

2

You just need a distinct

    SELECT ur.login_id, 
    GROUP_CONCAT(distinct ue.user_email_address) as 
    emails,
    GROUP_CONCAT(distinct up.phone_number) as 
     phones
     FROM greydeltadb.gd_user ur 
     LEFT JOIN greydeltadb.gd_user_info uri ON 
     uri.user_id=ur.user_id  
  INNER JOIN greydeltadb.gd_user_emails ue ON 
     ur.user_id = ue.user_id  
  INNER JOIN greydeltadb.gd_phone up  ON ur.user_id = 
  up.user_id  
  GROUP BY ur.login_id

answered Dec 25, 2018 at 11:13

Himanshu

3,9802 gold badges16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

krokodilko · Accepted Answer · 2018-12-25 11:12:16Z

1

Just use DISTINCT clause, see the documentation of GROUP_CONCAT:

> GROUP_CONCAT([DISTINCT] expr [,expr ...]
>              [ORDER BY {unsigned_integer | col_name | expr}
>                  [ASC | DESC] [,col_name ...]]
>              [SEPARATOR str_val])

In MySQL, you can get the concatenated values of expression combinations. To eliminate duplicate values, use the DISTINCT clause. To sort values in the result, use the ORDER BY clause.................

In case of your query, use just:

SELECT ur.login_id, 
      GROUP_CONCAT( DISTINCT ue.user_email_address ) as emails,
      GROUP_CONCAT( DISTINCT up.phone_number ) as phones
      .....

answered Dec 25, 2018 at 11:12

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2018-12-25 13:42:45Z

1

First, you are not using uri, so it is not needed in the query.

Second, you have a left join that is basically doing nothing. I suspect you want users, even those with no phones or no emails. If so:

SELECT ur.login_id,
       GROUP_CONCAT(DISTINCT ue.user_email_address) as emails,
       GROUP_CONCAT(DISTINCT up.phone_number) as phones
FROM greydeltadb.gd_user ur LEFT JOIN
     greydeltadb.gd_user_emails ue
     ON ur.user_id = ue.user_id LEFT JOIN
     greydeltadb.gd_phone up
     ON ur.user_id = up.user_id  
GROUP BY ur.login_id  ;

answered Dec 25, 2018 at 13:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

How do I combine multiple rows into one row in mysql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related