MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t1 (id int, name varchar(50));
create table t2 (name varchar(50), `type` varchar(50));
insert into t1 values (1, 'Roy');
insert into t1 values (1, 'Roy');
insert into t1 values (1, 'Roy');
insert into t1 values (1, 'Roy');
insert into t2 values ('Roy', 'GE');
insert into t2 values ('Roy', 'RA');
insert into t2 values ('Roy', 'SA');
insert into t2 values ('Roy', 'PA');
insert into t2 values ('Roy', 'PA');
SELECT A.ID, GROUP_CONCAT(B.type ORDER BY B.NAME separator ',')
FROM 
(SELECT t1.NAME, t1.ID
FROM t1
GROUP BY t1.NAME, t1.ID) A
JOIN 
(SELECT t2.NAME, t2.`type`
FROM t2
GROUP BY t2.NAME, t2.`type`) B
ON A.NAME = B.NAME
GROUP BY A.ID;


ID
GROUP_CONCAT(B.type ORDER BY B.NAME separator ',')


1
PA,SA,RA,GE