MySQL JOIN / GROUP_CONCAT second table?

Question

So I have this query that works perfectly:

SELECT users.*,
GROUP_CONCAT(categories.category_name) AS categories
FROM users
LEFT OUTER JOIN user_categories ON users.user_id = user_categories.user_id
LEFT OUTER JOIN categories ON user_categories.category_id = categories.category_id
WHERE users.user_city = 'brooklyn'
GROUP BY users.user_id
LIMIT 10;

Say I have another table that holds phone numbers, for the "users" a user can have any number of phone numbers... How would I go about doing round about the exact same thing I am doing wit the categories? In other words, I would like to get another column with ALL of the phone_numbers found in the "phones" table that have the same "user_id" and concat them together(phone1, phone2, phone3)? I have tried:

SELECT users.*,
GROUP_CONCAT(phones.phone_number) AS phone_numbers,
GROUP_CONCAT(categories.category_name) AS categories
FROM users
LEFT OUTER JOIN phones ON users.user_id = phones.user_id
LEFT OUTER JOIN user_categories ON users.user_id = user_categories.user_id
LEFT OUTER JOIN categories ON user_categories.category_id = categories.category_id
WHERE users.user_city = 'brooklyn'
GROUP BY users.user_id
LIMIT 10;

With no luck... or at least the query executes but it does some weird duplication thing... any help would be awesome!

Thanks!

Answer 1

It does weird things, becaue there is a cross product of certain rows. You can use the DISTINCT keyword to get only unique phone numbers:

GROUP_CONCAT(DISTINCT phones.phone_number) AS phone_numbers,

Check the documentation. Alternatively, you can get the phone numbers in another query where you would select only the phone numbers with a condition like WHERE phones.user_id IN (x, x, x, ...) (x are IDs returned from the first query).

Answer 2

This happened to me, I later had to alter my query to this.

SELECT 
   ( SELECT GROUP_CONCAT(`partnumber`) FROM `product_partnumber` AS `n` WHERE `p`.`id`=`n`.`product_id`) as `partnumbers`,
   ( SELECT GROUP_CONCAT(`oem`) FROM `product_oem` AS `n` WHERE `p`.`id`=`n`.`product_id`) as `oems`
FROM `product` AS `p`

So I had to use sub queries else I had the duplication.