Comparing two tables without foreign key [closed]

There are two tables in 2 different databases one table consists of Salutations example Mr, Miss and other table has names with Salutation example Mr.XYZ, Miss.ABC so I need a query which will compare the two tables and gives only those names from table 1 which are of matching salutations.

Example :

Table 1                       Table 2

Salutation column             Name column
Mr.,                          Mr.Abc
Miss.,                        Mr.XYZ
Mr. & Mrs.                    Mr. & Mrs. Def
                              Jr. xyz

So the query needs to compare two tables and give the output as

Mr.Abc,
Mr.Xyz
Mr. & Mrs. Def

Answer

I think you want join query with like:

select t2.*, t1.salutation
from table2 t2 join
     table1 t1
     on t2.name like concat(t1.saluation, '%');

The one caveat is that this can return more than one match. For instance, anything that matches “Mr. & Mrs.” would also match “Mr.”. To fix this, you can choose the longest match, using apply:

select t2.*, t1.salutation
from table2 t2 cross apply
     (select top (1) t1.*
      from table1 t1
      where t2.name like concat(t1.saluation, '%')
      order by len(t1.saluation) desc
     ) t1;