CODEWITHSUNDEEP
mysqlsql
Shafqat Shafi
Shafqat Shafi

Reputation: 1238

How do I combine multiple rows into one row in mysql

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.

Upvotes: 0

Views: 4885

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

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  ;

Upvotes: 1

Himanshu
Himanshu

Reputation: 3970

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

Upvotes: 2

krokodilko
krokodilko

Reputation: 36137

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
      .....

Upvotes: 1

Related Questions