I have three tables: articles, categories and articles_categories (which is a relationship table between the articles and categories tables, because an article can have several categories and a category can have many articles)
Screenshot of entity relationship diagram (phpmyadmin)
I want to make a query that shows me the information of all the articles, with a field that indicates all their categories.
I have this query:
SELECT
articles.*,
categories.name AS categoria, categories.id AS categoria_id
FROM articles_categories
LEFT JOIN articles ON articles_categories.article_id = articles.id
LEFT JOIN categories ON articles_categories.category_id = categories.id
ORDER BY articles.id;
Screenshot of the result of the above query
The previous query returns the information I want, but I can't group the categories so as not to have rows of repeated articles. How could I group the category query?
That is, for example in article 7 that has three categories. Instead of 3 rows, each with its category, you would need it to be on a single line with a field that includes all 3 categories you have. How should I do this?
Thank you very much
In the comments of the question @padaleiana has linked a question that deals with a similar situation.
The solution to my query is to use the function
GROUP_CONCAT
, and with it it is already possible to use the clauseGROUP BY
to group the results by article and have the information of all the categories.The resulting query is: