Insert multiple records with join -

i need in figuring out how insert more 1 records in table using join (when join returns more 1 values). here scenerio:

table a: a_id bigserial, role varchar(25), description varchar(25)

table b: b_id bigserial, role varchar(25), code varchar(25)

table , b connected column role.

example entries in table_a:

1, a, standard 2, b , test 3, c, test 4, d, standard 

example entries in table_b:

1, a, abc 2, b, xyz 3, c, xyz 4, d, abc 

basically need check roles description = test, insert entry custom role table_b code = abc (if entry doesn't exist already)

the following query give me test description roles not have entry code = abc in table b

query1:

select role table_a inner join table_b b on a.role=b.role a.description ='test' ,  b.code<>'abc'; 

i have following insert query:

insert table_b (role , code) select  (select role table_a inner join table_b b on a.role=b.role a.description ='test'and  b.code<>'abc'), 'abc'; 

the above insert query works when query1 returns 1 role, not sure how insert table_a when query1 returns more 1 results.

can pls help? not looking use stored procs same

thanks.

edited:

example entries in table_a:

1, a, standard 2, b , test 3, c, test 4, d, standard 5, e, test 

example entries in table_b:

1, a, abc 2, b, xyz 3, b, abc 4, c, def 5, c, xyz 6, d, abc 7, e, xyz 8, e, lll 

query1 not work here:

select role table_a inner join table_b b on a.role=b.role a.description ='test' ,  b.code<>'abc'; 

using query now:

select distinct role tb  role not in ( select b.role ta inner join tb b on a.role=b.role a.description =test ,  b.code=abc) , role in (select role ta description =test); 

how insert work now?

you can make column 'code'.

something like: insert table_b (role , code) select role, 'abc' code table_a inner join table_b b on a.role=b.role a.description ='test' , b.code<>'abc';

so number of columns match.