CODEWITHSUNDEEP
mysqlgroup-byjoingroup-concat
Koen
Koen

Reputation: 48

MySQL group_concat with 2 joins returns unwanted results

When executing this query i expect te get 2 mobilenumbers and 1 category, instead i get 2 categories, what am i doing wrong? I guess it has to do with the way i am joining things?

User, can have multiple imei's, categoryjoin links a user to multiple categories

SELECT 
    u.*, 
    group_concat(i.mobilenumber) as mobilenumbers,
    group_concat(c.name) as categories 
FROM 
    users AS u 
INNER JOIN 
    categoryjoin AS cj 
ON 
    u.uid = cj.user_id 
INNER JOIN 
    categories AS c 
ON 
    cj.category_id = c.uid 
INNER JOIN 
    imei AS i 
ON
    u.uid = i.user_id
GROUP BY 
    u.uid

Big pre-thanks you for your help!

Upvotes: 0

Views: 405

Answers (1)

Ike Walker
Ike Walker

Reputation: 65537

If a user matches one category, but matches 2 rows in imei, then the category will be duplicated in the result set. You can get rid of redundant values from group_concat using DISTINCT:

SELECT 
    u.*, 
    group_concat(distinct i.mobilenumber) as mobilenumbers,
    group_concat(distinct c.name) as categories

Upvotes: 2

Related Questions