September 9, 2009 at 12:35 pm
I have two tables. One has 2 fields such as FirstName and LastName. the other only has Name, with both first and last name concatenated. The latter one may also have Middle names and middle initials.
Table1
LastName, FirstName
Johnson, Peter
Miller, Pam
Conner, Sam
Table2
Name
Peter M. Johnson
Pam Susie Miller
Sam Mike Conner
Is there are way to use the LIKE operator and join these tables based on Name?
Thanks!
September 9, 2009 at 12:53 pm
The following code should work.
However, I strongly recommend to rethink the concept of your data storage, especially if you want to join tables based on those values...
Also, you need to make sure to handle duplicates properly (e.g. if you insert 'Peter F. Johnson' into @Table2, it's getting hard to tell who you talk about when referencing 'Johnson, Peter' in @Table1...)
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*/
September 9, 2009 at 1:01 pm
EDIT: Misread what lmu was saying
September 9, 2009 at 1:24 pm
Ok, i see what i did wrong. I tried to do the comparison in the where clause because i wasn't sure i could use LIKE in the ON clause
Thanks!
FYI: I inherited this table and I'm using it only to get the information in need.
Thanks again!!!
September 9, 2009 at 1:43 pm
By the way i do have some William Smiths and some are ambiguous. I don't know what will happen when i need to join them. Maybe I'll just exclude them if they cause a problem.
September 9, 2009 at 3:24 pm
Jacob Pressures (9/9/2009)
By the way i do have some William Smiths and some are ambiguous. I don't know what will happen when i need to join them. Maybe I'll just exclude them if they cause a problem.
What's going to happen is the following:
You'll end up with duplicate entries for first and last name = 'Bill', 'Smith', assuming there's more than one entry in Table1 (which would be a bad habit if there'd be no addtl. columns to distinguish the two...).
You'll also end up with dups if you'd have one 'Bill','Smith' and more than one row in Table" matching the pattern 'like 'Bill%Smith'.
Finally, you'd end up with a cross join of those rows if you'd have dups in both tables.
(E.g. two 'Will', 'Smith' in Table1 vs. 'Bill W. Smith' and 'Bill Smith' in Table2 will result in four rows.)
Excluding such data from a query is a bad habit, too. I strongly recommend to think about normalize your table(s) rather than use the too often seen cover-bad-data-by-procedural-code method. Are you sure you're going to remember that you excluded 'Bill Smith' from your query a year from now???
Regarding your comment mentioning the WHERE clause:
The following two statements will result in the same query plan (at least with the small set of data). Therefore, I'd consider those two identical.
So, it seems like there was an issue when defining the WHERE condition...
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply