Group concatenate from multiple tables in mysql Ver 15.1

Question

I have multiple tables with table 1 containing the primary id key. I am concatenating all tables based on the id. But this doesn't give me the desired output.

 Table1
id account type date
1 234w R 2020-01-01
2 567 FD 2020-05-07
3 678gh FD 2020-09-10

Table2
id designation
2 customer
3 employee
3 manager

Table3
id state
1 UP
2 AP
3 UK

This is what I tried

SELECT CONCAT(`account`,"/",`type`,"/",`date`),
GROUP_CONCAT(Table2.designation SEPARATOR "/") AS t2,
GROUP_CONCAT(Table3.state SEPARATOR "/") AS t3,
FROM Table1 t1
LEFT JOIN table1 ON t1.id=t2.id
LEFT JOIN table1 ON t1.id=t3.id
GROUP BY t1.id

Expected output
234w/R/2020-01-01 NULL UP
567/FD/2020-05-07 CUSTOMER AP
678gh/FD/2020-09-10 EMPLOYEE/MANAGER UK

Answer 1

As I understand your question, there may be multiple matches in both tables. In that case, I would recommend pre-aggregation. I find that it's easy to express this with subqueries:

select concat_ws('/', account, type, date) as res,
    (select group_concat(designation separator '/') from table2 t2 where t2.id = t1.id) as designations,
    (select group_concat(state       separator '/') from table3 t3 there t3.id = t1.id) as states
from table1 t1

Note that concat_ws() comes handy to shorten the first concat().