sql query select combined select from multiple table

Question

I really don't know what the title should be, but here the question I have 2 table (actually more)

 table a               table b  
id (pk)| country     id(fk)| Branch
------ | ------      ------|--------
01     | Indonesia   01    | Jakarta
                     01    | Bali

if i do select * from a,b where a.id=b.id the result will be

id | Country  |Branch
01 | Indonesia|Jakarta
01 | Indonesia|Bali

I want the result to be like below

id | Country   | Branch
01 | Indonesia | Jakarta,Bali

Is it possible? I dont really trying to do research(i mean searching) as I don't know what keyword should I search

Already answered here : http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Mr. Laststringx, Feb 17 '17 at 07:14
Thank you @Scovetta pls post as an answer so i can flag answered — Alexander Chandra, Feb 17 '17 at 07:16
already solved just need the keyword... im using postgres btw — Alexander Chandra, Feb 17 '17 at 07:59

Scovetta · Accepted Answer · 2017-02-17T18:45:02.177

When using MySQL, GROUP_CONCAT is the function you're looking for.

Set up the tables like in the request above:

create table a (
  `id` int(15) auto_increment primary key,
  `country` varchar(200)
);

create table b (
  `id` int(15) not null,
  `branch` varchar(200),
  foreign key(`id`) references a(`id`)
);

insert into a values (1, 'Indonesia');
insert into b values (1, 'Jakarta');
insert into b values (1, 'Bali');

Perform the query:

select a.id, 
    a.country, 
    group_concat(distinct b.branch) as 'branch'
from a 
    left join b on a.id=b.id;

Output:

| id |   country |       branch |
|----|-----------|--------------|
|  1 | Indonesia | Jakarta,Bali |

sql query select combined select from multiple table

1 Answers1