Select from 4 tables with joins and IN

Question

I'm have 5 tables.
First products like:

id | country_ids | category_ids | users_ids  
1  | 1,4,6       |  4,5,6,70    | 5,6,9  
2  | 5,6,3       |  4,8,2,11    | 1,5,8  

Second countries like:

c_id | c_name  
1  | Åland Islands  
2  | Antarctica  
...  

Third categories like:

cat_id | cat_name  
2  | Small  
4  | Large    
...  

Fourth table users like:

u_id | u_name  
1  | David  
2  | Mary    
...  

And fifth table review(table structure isn't important, count id's only).

and sql

    SELECT a.*, COUNT(b.comm_id) AS comm_count, c.*, d.*, e.*
    FROM products AS a
    LEFT JOIN comments AS b ON b.comm_prod_id = a.id AND b.comm_published = 1
    LEFT JOIN countries AS c ON c.c_id IN (a.country_ids)
    LEFT JOIN categories AS d ON d.c_id IN (a.category_ids)
    LEFT JOIN users AS e ON e.c_id IN (a.users_ids)
    /*WHERE published = 1*/
    GROUP BY id
    ORDER BY id DESC
    LIMIT 0, 5

But this query return only first value for joined tables.

How can i get row like

1 | Åland Islands, Equador, Russia | Small, tiny, large, ... | Anna, John, Linda  

PS! Or do i need to create a table relationship for each table? What is very not like.

Answer 1

Use GROUP_CONCAT() function:

SELECT 
    a.id, 
    GROUP_CONCAT(DISTINCT c_name) AS country_names, 
    GROUP_CONCAT(DISTINCT cat_name) AS cat_names, 
    GROUP_CONCAT(DISTINCT u_name) AS user_names, 
    COUNT(DISTINCT b.comm_id) AS comm_count
FROM products AS a
LEFT JOIN comments AS b ON b.comm_prod_id = a.id AND b.comm_published = 1
LEFT JOIN countries AS c ON c.c_id IN (a.country_ids)
LEFT JOIN categories AS d ON d.c_id IN (a.category_ids)
LEFT JOIN users AS e ON e.c_id IN (a.users_ids)
/*WHERE published = 1*/
GROUP BY id
ORDER BY id DESC
LIMIT 0, 5

Update: Oh man, you have a comma separated list in your table. It sucks.
Read about normalization and create relation tables with structure product_comments( product_id, comment_id), product_countries( product_id, country_id) and store each relation in separate row.

Example data:

product_countries  
product_id, country_id  
1, 1
1, 4
1, 6
2, 5
2, 6
2, 3