By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE MainTbl(UNDERWRITER_CODE int, UNDERWRITER_NAME varchar(100))
INSERT INTO MainTbl VALUES
(NULL,'dylan.campbell'),
(NULL,'dylanadmin'),
(NULL,'dylanc'),
(002,'Dylan Campbell'),
(002,'dylan.campbell'),
(002,'dylanadmin'),
(NULL,'scott.noffsinger'),
(001,'Scott Noffsinger')
CREATE TABLE LKP(UNDERWRITER_CODE int, UNDERWRITER_NAME varchar(100))
INSERT INTO LKP VALUES
(002,'Dylan Campbell'),
(001,'Scott Noffsinger')
10 rows affected
select mt.*, l.*
from maintbl mt outer apply
(select top (1) lkp.*
from lkp
order by difference(mt.underwriter_name, lkp.underwriter_name) desc
) l
| UNDERWRITER_CODE | UNDERWRITER_NAME | UNDERWRITER_CODE | UNDERWRITER_NAME |
|---|---|---|---|
| null | dylan.campbell | 2 | Dylan Campbell |
| null | dylanadmin | 2 | Dylan Campbell |
| null | dylanc | 2 | Dylan Campbell |
| 2 | Dylan Campbell | 2 | Dylan Campbell |
| 2 | dylan.campbell | 2 | Dylan Campbell |
| 2 | dylanadmin | 2 | Dylan Campbell |
| null | scott.noffsinger | 1 | Scott Noffsinger |
| 1 | Scott Noffsinger | 1 | Scott Noffsinger |