Need Help 'Joining' 2 tables

I have 1 table with a Name field  containing data like 'Smith, John'
I have another tablw with a LastName & FirstName field containing data like 'Smith', 'John'

How can I write a Select statement to Join on Name and Last/Firstname so I return the matching records?

Thanks in advance
LVL 1
JElsterAsked:
Who is Participating?
 
solution46Connect With a Mentor Commented:
Hmm... not easily unless your data is absolutely spot on.

However, if it is, this should work...

SELECT t1.FullName, t2.LastName, t2.FirstName
FROM [FullNameTable] t1
    INNER JOIN [PartNameTable] t2
        ON t1.FullName = t2.LastName + ', ' + t2.FirstName


Regards,

s46.
0
 
Thandava VallepalliConnect With a Mentor Commented:
SELECT *
FROM TABLE1 T1, TABLE2 T2
WHERE T1.NAME = T2.LASTNAME + ',' + T2.FIRSTNAME

0
 
Thandava VallepalliCommented:
SELECT *
FROM TABLE1 T1, TABLE2 T2
WHERE T1.NAME = T2.LASTNAME + ', ' + T2.FIRSTNAME
0
 
OlegPCommented:
SELECT *
FROM TBL1 INNER JOIN  TBL2  
ON
TBL1.NAME LIKE '%'+TBL2.LASTNAME+'%'
AND
TBL1.NAME LIKE '%'+TBL2.FIRSTNAME+'%'
0
 
KarinLoosCommented:
or

select a.* , b.*
from  tablea a  ,
         tableb b  
where a.[name] like '%' + b.[firstname] + '%'
and     a.[name] like '%' + b.[lastname] + '%'
0
All Courses

From novice to tech pro — start learning today.