I need help writing a mysql query that combines 3 data tables codeiniger 4

Question

I have 3 mysql database tables including:

article ( id | name )

id	name
1	article 1
2	article 2
3	article 3

category ( id | name )

id	name
1	category 1
2	category 2
3	category 3

article_category ( id | article_id | category_id )

One article can combine many categories

id	article_id	category_id
1	1	1
2	1	2
3	2	1
4	2	3

My task is to retrieve a list of records (no duplicates) in the article table with the name field (denoted categoryName) and id field (denoted categoryId) of the category table grouped together separated by commas. " , " (same as group_concat() statement) The desired result will look like this:

id	name	categoryName	categoryId
1	article 1	category 1,category 2	1,2
2	article 2	category 1,category 3	1,3
3	article 3

help me write this mysql query. Thank you!

Answer 1

You need

• LEFT JOIN (as you want all the articles irrespective of they are mapped to category or not) and
• GROUP BY(as you need one record per article) with
• GROUP_CONCAT(as you need comma-separated list of category id and name)

Use the following query:

select a.id, a.name,
       group_concat(c.name) as category_name,
       group_concat(c.id) as category_id
  from article a
 left join article_category ac on ac.article_id = a.id
 left join category c on ac.category_id = c.id
group by a.id, a.name
order by a.id