group_concat by combining column values of two table-CodePudding

I am trying to combine columns between two tables and concat them. While I am able to use group_concat on the columns which are specific to just one table but there is also a column for which I need to combine from table1.role_id and table2.role_id and join the result with specific delimiter.

I am using sqlite db.

SQL Fiddle: link

Table1

name	role_id	dep_id
a	1	d1
a	1	d2
b	2	d3
b	3	d5
c	3	d4

Table2

name	role_id	ipv6	ipv4
a	1	ipv6_1	ipv4_1
a	2	ipv6_2	ipv4_1
b	7	ipv6_3	ipv4_2
b	7	ipv6_7	ipv4_8
e	2	ipv6_4	ipv4_3

The query I have come up with

select t1.name,
          -- combine role_id column from both the tables and do group_concat on it??
          group_concat(DISTINCT t1.dep_id),
          group_concat(DISTINCT t2.ipv6),
          group_concat(DISTINCT t2.ipv4)
from       table1 t1
inner join table2 t2
        on t1.name = t2.name
group by t1.name

Current query output

name	group_concat(DISTINCT t1.dep_id)	group_concat(DISTINCT t2.ipv6)	group_concat(DISTINCT t2.ipv4)
a	d1,d2	ipv6_1,ipv6_2	ipv4_1
b	d3,d5	ipv6_3,ipv6_7	ipv4_2,ipv4_8

Expected Result

name	CONCAT_role_id	group_concat(DISTINCT t1.dep_id)	group_concat(DISTINCT t2.ipv6)	group_concat(DISTINCT t2.ipv4)
a	1,2	d1,d2	ipv6_1,ipv6_2	ipv4_1
b	2,3,7	d3,d5	ipv6_3,ipv6_7	ipv4_2,ipv4_8

CodePudding user response：

Instead of a join use UNION ALL:

WITH
  common_names AS (SELECT name FROM table1 INTERSECT SELECT name FROM table2),
  cte AS (
    SELECT name, role_id, dep_id, null ipv6, null ipv4 FROM table1
    UNION ALL
    SELECT name, role_id, null, ipv6, ipv4 FROM table2
  )
SELECT name,
       GROUP_CONCAT(DISTINCT role_id) roles,
       GROUP_CONCAT(DISTINCT dep_id) deps,
       GROUP_CONCAT(DISTINCT ipv6) ipv6s,
       GROUP_CONCAT(DISTINCT ipv4) ipv4s
FROM cte
WHERE name IN common_names
GROUP BY name;

See the demo.