Skip to content
Advertisement

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.

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

the result is as I wanted

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

I want some result like this:

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:

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement