By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table1
(id int,name varchar(10),phone int)
insert into table1(id,name,phone)
values(1,'abc',85435),
(2,'def',87765)
select * from table1
| id | name | phone |
|---|---|---|
| 1 | abc | 85435 |
| 2 | def | 87765 |
create table address
(id int,refid int,addr varchar(10))
create table category
(id int,refid int,cat varchar(10))
insert into address(id,refid,addr)
values(1,1,'1ad1'),
(2,1,'1ad2'),
(3,1,'1ad3'),
(4,1,'1ad4'),
(5,2,'2ad1'),
(6,2,'2ad2')
insert into category(id,refid,cat)
values(1,1,'1cat1'),
(2,1,'1cat2'),
(4,1,'1cat3'),
(5,2,'2cat1'),
(6,2,'2cat2')
select t.id,name,phone,
group_concat(distinct a.addr separator ',') as address,
group_concat(distinct c.cat separator ',') as category
from table1 t
left join address a
on t.id = a.refid
left join category c
on t.id = c.refid
group by t.id,name, phone
| id | name | phone | address | category |
|---|---|---|---|---|
| 1 | abc | 85435 | 1ad1,1ad2,1ad3,1ad4 | 1cat1,1cat2,1cat3 |
| 2 | def | 87765 | 2ad1,2ad2 | 2cat1,2cat2 |
select t.id,name,phone,
(select group_concat(a.addr separator ',')
from address a
where a.refid = t.id) as address,
(select group_concat(c.cat separator ',')
from category c
where c.refid = t.id) as category
from table1 t
| id | name | phone | address | category |
|---|---|---|---|---|
| 1 | abc | 85435 | 1ad1,1ad2,1ad3,1ad4 | 1cat1,1cat2,1cat3 |
| 2 | def | 87765 | 2ad1,2ad2 | 2cat1,2cat2 |