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
Advertisement
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