SQL phpmyadmin SELECT and return multiple fields as string concat in a column of parent SELECT

I have 3 tables. users, contents and categories. so a user writes a content and selects a category for that.

categories                       users
--------------------------       ----------------------
category_id  category_name       user_id user_name ...

contents
------------------------------------------------
content_id content_creator content_category ...

so there are two references in contents table.

content_creator => user_id
content_category => category_id

WHAT I NEED: I want to have a list of all the categories, and for each row I want to show the count of contents containing that category and the users which used this category in their contents.

WHAT I DID:

I tried this query and it’s working fine

SELECT ca.category_id,ca.category_name,
        COUNT(c.content_id) AS contents_count,
        COUNT(c.content_creator) AS contents_creators_count
        FROM categories ca
        JOIN contents c ON (ca.category_id = c.content_category)
        GROUP BY content_category

the result is as I wanted

category_id  category_name  contents_count  contents_creators_count
6            cat1           6               3
8            cat2           1               1
9            cat3           1               1
10           cat4           2               2
11           cat5           2               2

QUESTION: can I have all the users.user_id in form of a string or an array as a column in result?

like I tried this but it works for only one row

SELECT ca.category_id,ca.category_name,
COUNT(c.content_id) AS contents_count,
(
    SELECT u.user_id,u.User_name
    FROM users u
    JOIN contents c ON (u.user_id = c.content_id)
    JOIN categories ca ON (c.content_category=ca.category_id)
    
)AS category_users
FROM categories ca
JOIN contents c ON (ca.category_id = c.content_category)
GROUP BY ca.category_id

I want some result like this:

category_id  category_name  contents_count  category_users //based on user_id
6            cat1           6               63,88,125
8            cat2           1               63
9            cat3           1               88
10           cat4           2               88,70
11           cat5           2               null //like this category not used

Answer

You seem to want string aggregation: MySQL offers GROUP_CONCAT() for this. If you want the list of (distinct) content creators for each content, you do not need to to bring the users table; that information is available in contents already.

So:

select ca.category_id, ca.category_name, 
    count(*) as contents_count,
    group_concat(DISTINCT content_creator ORDER BY content_creator) category_users 
from categories ca
inner join contents c on ca.category_id = c.content_category
group by ca.category_id

Advertisement

Answer