DECLARE @Table1 TABLE (LastName varchar(30), FirstName varchar(30))
INSERT INTO @Table1 
SELECT 'Johnson', 'Peter' UNION ALL 
SELECT 'Miller', 'Pam' UNION ALL 
SELECT 'Conner', 'Sam'

declare @Table2 TABLE (fullname varchar(100))
INSERT INTO @Table2 
SELECT 'Peter M. Johnson' UNION ALL 
SELECT 'Pam Susie Miller' UNION ALL 
SELECT 'Sam Mike Conner'

SELECT * FROM @Table1 t1 INNER JOIN @Table2 t2 ON t2.fullname LIKE t1.FirstName +'%'+LastName

/* result set
LastName	FirstName	fullname
Johnson	Peter	Peter M. Johnson
Miller	Pam	Pam Susie Miller
Conner	Sam	Sam Mike Conner*/

SELECT * FROM #Table1 t1 INNER JOIN #Table2 t2 ON t2.fullname LIKE t1.FirstName +'%'+LastName
--and
SELECT * FROM #Table1 t1 CROSS JOIN #Table2 t2 
WHERE t2.fullname LIKE t1.FirstName +'%'+LastName