How to use GROUP_CONCAT on multiple JOIN

Question

I am currently retrieving data from multiple tables using a cus_id as the initial query. From here I have multiple tables that can have various rows of data that I would like to retrieve and use GROUP_CONCAT to return this data in a single row. At the moment, my query is returning duplicate data based on the max number of rows returned by one of the group concats.

SELECT a.id,c.x,c.y,c.z

GROUP_CONCAT(a.column_a) AS aca, 
GROUP_CONCAT(a.column_b) AS acb, 
GROUP_CONCAT(b.column_a) AS bca, 
GROUP_CONCAT(b.column_b) AS bcb, 

FROM `table_a` a
INNER JOIN `table_b` b ON a.id = b.id
INNER JOIN `table_c` c ON a.id = c.id
WHERE a.id = ?

Also, in this scenario, what is the correct join method to use. I am expecting all the fields I am requesting to have some sort of data.

Looking at your original query, I wonder why you are doing this. It seems you are trying to solve some display issue that you should better care about in your GUI layer (app or Website). When doing this in SQL, be aware that you lose relations. An example: table b contains the rows (1,10), (2,20), (3,30). Your `GROUP_CONCAT` results may then be ('1,3,2', '30,20,10'). There is no guaranteed order unless you specify it: `GROUP_CONCAT(col_a ORDER BY b_id)`. — Thorsten Kettner, Mar 04 '20 at 06:27

Eric M · Accepted Answer · 2020-03-04T06:02:56.303

Problem was resolved by using sub queries to isolate the GROUP_CONCAT requests. This allowed me to get only the data I wanted without duplicate results manipulated by other JOIN requests.

SELECT a.id,c.x,c.y,c.z 

(SELECT GROUP_CONCAT(column_a) FROM table_a) AS aca, 
(SELECT GROUP_CONCAT(column_b) FROM table_a) AS acb, 
(SELECT GROUP_CONCAT(column_a) FROM table_b) AS bca, 
(SELECT GROUP_CONCAT(column_b) FROM table_b) AS bcb,

FROM table_a a
INNER JOIN `table_c` c ON a.id = c.id
WHERE a.id = ?

Thorsten Kettner · Answer 2 · 2020-03-04T06:30:04.633

Aggregate before joining. Somthing along the lines of:

select
  a.*,
  b.grp_a,
  b.grp_b,
  c.grp_x,
  b.grp_y
from table_a a
join
(
  select
    a_id,
    group_concat(a order by b_id) as grp_a,
    group_concat(b order by b_id) as grp_b
  from table_b
  group by a_id
) b on b.a_id = a.id
join
(
  select
    a_id,
    group_concat(x order by c_id) as grp_x,
    group_concat(y order by c_id) as grp_y
  from table_c
  group by a_id
) c on c.a_id = a.a_id
order by a.a_id;

How to use GROUP_CONCAT on multiple JOIN

2 Answers2