MySQL JOIN / GROUP_CONCAT second table?

Question

So, I have this query that works fine:

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; 

Let's say I have another table that stores phone numbers, since "users" can have any number of phone numbers ... How do I get around the same thing that I do with categories? In other words, I would like to get another column with all the phone numbers found in the phone table that have the same "user_id" and combine them together (phone1, phone2, phone3)? I 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; 

No luck ... or at least the request is being executed, but it does some weird thing for duplication ... any help would be awesome!

Thanks!

Answer 1

This is strange because there is a cross product of certain lines. 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 phone numbers in another request, where you will select only phone numbers with a condition like WHERE phones.user_id IN (x, x, x, ...) (x are the identifiers returned from the first request).

Answer 2

This happened to me, later I had to change my request.

 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` 

Therefore, I had to use additional queries, I had duplication.