postgresql - Join column from a table with concat columns from another one

Question

I try to match names from a table with a concatenation of columns in another table with Postgres.

What I have:

Table A:

id,name
1,John Smith
2,Laura Doe Van Renburg
3,Laura Thorpe
4,Carl Leonard Dong

Table B:

id,firstname,lastname
1,Aloys,Smith
2,Laura,Doe Van Renburg
3,Pedro,De Mung
4,Carl Leonard, Dong

The result I expect

Laura Doe Van Renburg
Carl Leonard Dong

What I tried

I think concatening the columns firstname and lastname from table B could help but I can't figure out what the correct syntax is.

select A.name from A 
  join (select concat(firstname,' ',lastname) from B) as firstandlast 
  on a.name = firstandlast;

But it's not the correct way. Any clue would be welcome!

question from:https://stackoverflow.com/questions/65931464/join-column-from-a-table-with-concat-columns-from-another-one

Answer 1

You were close:

select a.name 
from table_a a
  join table_b b on concat(b.firstname, ' ', b.lastname) = a.name