CODEWITHSUNDEEP
sql-servermatchwhere-clause
user3052850
user3052850

Reputation: 178

SQL: Where Clause Match Fullname with First & LastName

I have two tables, which I need to match on 

"Fullname"

against

 "FirstName" & "LastName"

and extract the userID from the "FirstName"/"LastName" table. If there's a match retrieve the UserID if not Just Null

Example: Table1 (With fullname)

|Sam Smith|

Must match with Table2 (with first and last name)

| Sam | Smith |

And I would like to take into account if a person has three names. (Fullname)

 |Sam Samual Smith|

vs. (First & Last Name)

|Sam Samual | Smith |

Any help needed, not sure how to go around it,

Upvotes: 0

Views: 1843

Answers (2)

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

As Lasse V. Karlsen suggested,

  SELECT *
  FROM [MainTable] M
  INNER JOIN [SubTable] S
  ON M.Fullname=S.Firstname + ' ' + S.Lastname; -- check if fullname is a of combination 
                                                -- firstname and lastname from other table

Replace the table names with your table names and put the fields you want in the select query as selecting all the fields could compromise the perfomance.

Upvotes: 1

StackUser
StackUser

Reputation: 5398

Try like this,

SELECT *
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.FullName = T2.FirstName + ' ' + T2.LastName

Upvotes: 0

Related Questions