how to match two table without key based on my case

Question

if i have two table which is contain the first_name and last name column. how to find out the customer id in based_table match with the match_table
for the below example, the based_table recordID=552111, which contain the first_name in the match_table

i would like to match the possible first_name so i need to use first_name like '%match_table.first_name%' to find out the recordid. but now i don't the the key to link with two table. how can i do that to search with the possible first_name

based_table, match_table

based_table
recordID,first_name,last_name
123456,billy,chan
552111,amy aa,law

match_table
first_name,last_name
billy,chan
amy,law

my expected output

recordID,based_table.first_name,based_table.last_name,match_table.first_name,match_table.last_name
123456,billy,chan,billy,chan
552111,amy aa,law,amy,law

Answer 1

Hi @nononame2021 ,

try this code:

select case  when m.last_name is null then null   
                            when m.last_name is not null then cast([recordID] as varchar(20))   
                            end as [recordID],  
b.first_name as [based_table.first_name] ,  
b.last_name as [based_table.last_name],  
m.first_name as [match_table.first_name],  
m.last_name as [match_table.last_name]  
from based_table b   
join match_table m   
on b.first_name like '%'+m.first_name+'%'  

output:

Best regards
Niko

Answer 2

Hi @nononame2021 ,

check this code:

select * from based_table b join match_table m on b.firstname like '%'+m.firstname+'%'  

Best regards
Niko

Answer 3

based_table
recordID,first_name,last_name
123456,billy,chan
552111,amy aa,law
555555,jenny,chan
666666,XXXXXR,TSE

match_table
first_name,last_name
billy,chan
amy,law
jenny,<empty>
'',XXXXXCHIU AU

select * from based_table b
left join match_table m on b.firstname+' '+b.last_name like '%'+m.last_name+'%' where m.last_name='';

now the output is below
recordID,based_table.first_name,based_table.last_name,match_table.first_name,match_table.last_name
666666,XXXXXR,TSE,'',XXXXXCHIU AU

why it can retrieve the recordid in the output for record in match_table last_name='XXXXXCHIU AU' seem it can't meet this criteria
match_table m on b.firstname+' '+b.last_name like '%'+m.last_name+'%'

my exptected out is below:
recordID,based_table.first_name,based_table.last_name,match_table.first_name,match_table.last_name
'',XXXXXR,TSE,'',XXXXXCHIU AU