Getting Last name from a full name using two tables

Transact-SQL
1

Hi All,

I have two tables:

TableA: employeeID, Full Name (full name might include middle names, or abbreviation together with a First and last name )
example:
employee ID: 001
Full Name: Alexander (Alex), The Great, III Mr. Macedon

TableB: First Name, Last Name, email

example:
First name: Alex
Last Name: Macedon
email: alexm@domain.com

I need to get the employeeID joined with a data from Table B
example:
employeeID: 001
First name: Alex
Last Name: Macedon
email: alexm@domain.com

I have tried the following query:

select A.EmployeeID, B.[First name], B.[Last name], B.[Email]
from TableA as A
Left join TableB as B
on B.[Last name] like '%'+A.[Full Name]+'%'

But I get all B columns as NULL

Thank you in advance,

2

Very unlikely that you can do that with any degree of confidence that the matches are accurate.

If you have, say, two John Smith then your LEFT JOIN will match both of them, so you will get two rows in the resultset [for EACH of the John Smith records in TableA) instead of one. You can program against that (e.g. by checking if there are multiple row matches in TableB and either just selecting the first one (that's going to be ambiguous at best!!) or returning some sort of Flag / Message indicating "The result is ambiguous"

To fix your query, as it stands, turn the JOIN condition round:

ON A.[Full Name] like '%'+B.[Last name]+'%'
1 Like
3

That actually works!

4

Not when there are two JOHN SMITHs it doesn't !!!

5

Agree, but in my case it's should be ok :slight_smile: Thanks!

6

Problem with that is that that is a "now" answer, whereas it is the "in future" situation which is the risk. So if this is a one-off to fix something then, with careful checking, it will be fine. One improvement would be to check if there was more than one match, on LastName, and if so include that on an exceptions list, of some sort, for a human to check.

Going forwards you need to get the EmployeeID into TableB - e.g. by allowing a user to select the appropriate employee using some sort of search function that shows DUPs. This will only work reliably with some sort of unique criteria on which you can join the two tables, otherwise the risk remains of course.

1 Like