Search code examples
mysqlsqljoindistinctgroup-concat

Repeated values with GROUP_CONCAT without using DISTINCT on MySQL query with multiple joins

I have a schema like the one below and I'm trying to make a query to get all filters and all project_items from a single project, so that the results are just in one line by using GROUP_CONCAT.

filters

  • id
  • title

project_filters

  • id
  • filters_id
  • projects_id

projects

  • id
  • title

project_items

  • id
  • title
  • projects_id

the tables are connected like this

[filters] 1--N [project_filters] N--1 [projects] 1--N [project_items]

the query and the problem

  • SELECT
  • projects.id,
  • projects.title,
  • GROUP_CONCAT(project_items.title) AS items_title,
  • GROUP_CONCAT(filters.title) AS filters_title
  • FROM projects
  • LEFT JOIN project_items ON project_items.projects_id=projects.id
  • LEFT JOIN project_filters ON projects.id=project_filters.projects_id
  • LEFT JOIN filters ON filters.id=project_filters.filters_id
  • WHERE projects.id="1"

Project 1 has 3 filters (1, 2 and 3) and 3 items (1, 2 and 3), which is resulting in repeated values (9 for each), like this:

  • id = 1
  • title = project 1
  • items_title = title 1, title 2, title 3, title 1, title 2, title 3, title 1, title 2, title 3
  • filters_title = filter 1, filter 2, filter 3, filter 1, filter 2, filter 3, filter 1, filter 2, filter 3

Can anybody point me in the right direction? I would like to know how to make the query return the filters and items without repeating.

I think the solution is to arrange the joins somehow, but I can't figure it out.

OBS: GROUP_CONCAT with DISTINCT doesn't fit because there might be repeating titles

Thank you very much!

Solution

  • Use subqueries for each of GROUP_CONCATs:

    SELECT i.id, i.title, i.items_title, f.filters_title FROM
( SELECT
  projects.id,
  projects.title,
  GROUP_CONCAT(project_items.title) AS items_title
  FROM projects
  LEFT JOIN project_items ON project_items.projects_id=projects.id
  group by projects.id,
  projects.title) as i
INNER JOIN
( SELECT
  projects.id,
  projects.title,
  GROUP_CONCAT(filters.title) AS filters_title
  FROM projects
  LEFT JOIN project_filters ON projects.id=project_filters.projects_id
  LEFT JOIN filters ON filters.id=project_filters.filters_id
  group by projects.id,
  projects.title) as f
ON i.id = f.id
WHERE i.id="1"