2

The Query:

SELECT MemberId, a.MemberName, GROUP_CONCAT(FruitName) FROM a LEFT JOIN b ON 
a.MemberName = b.MemberName GROUP BY a.MemberName

Table a

MemberID          MemberName              
--------------    ----------  
1                  Al                   
1                  Al       
3                  A2       

Table b

MemberName             FruitName
---------------        --------------
Al                     Apple
Al                     Mango
A2                     Cherry

Resulting Output from above query:

MemberId    MemberName  GROUP_CONCAT(FruitName)
3           A2          Cherry
1           A1          Apple,Apple,Mango,Mango

The actual tables I am using have 10 columns apiece so just storing everything in one table is not a workaround. That said, how can I change the query to only return 'Apple,Mango' for MemberName?

NorthCat
  • 9,643
  • 16
  • 47
  • 50
Adam
  • 405
  • 1
  • 5
  • 22

3 Answers3

8

Add the keyword DISTINCT to the grouped column:

GROUP_CONCAT(DISTINCT FruitName)
soulmerge
  • 73,842
  • 19
  • 118
  • 155
5

try

GROUP_CONCAT(Distinct FruitName)
Stan R.
  • 15,757
  • 4
  • 50
  • 58
3
SELECT  MemberId, a.MemberName, GROUP_CONCAT(DISTINCT FruitName)
FROM    a
LEFT JOIN
        b
ON      a.MemberName = b.MemberName
GROUP BY
        a.MemberName
Quassnoi
  • 413,100
  • 91
  • 616
  • 614