How to join with a like

I have the following tables:

Table 1 ---
Name     State   Eligible
Bob         FL        Y
Rob        GA        N
Tom       VA        N


Table 2 ---
Name     State   Eligible
Bobby     FL         Y
Robert    GA        N
Thomas  VA      

If table 1 name is like table two name then update table two with the correct eligible status (as shown).  If the names don't match the like then don't populate the table 2 eligible.

Any ideas on how to do this?  I know you can easily do a join but I don't know how to do it with a "like" involved.

Thanks!

Andrew

Also, as a reference, below is the actual query which uses the join, not the like!
UPDATE X_BHCGSW_TIN INNER JOIN Sheet1 ON X_BHCGSW_TIN.TIN = Sheet1.TIN SET Sheet1.Overall_HPN = "Y"
WHERE (((X_BHCGSW_TIN.Overall_HPN)="PAR_HPN"))
ajslentzAsked:
Who is Participating?
 
MikeWalshCommented:
That is not going to be pretty, and you will find incorrect cases with the more names you add..

The original above will cause issues if there are duplicate first names (VERY possible) or similar starting names (Ronald and Rondell)

The second modified example you gave is even worse because that will give off false positives for what I described above, plus any names that contain portions of smaller names..

Let's see you have a "Li" in one table.. and you have a "Allison" in the second.. Well now you just joined those two records assuming you have the same names.

You need to handle this in adifferent way, and if you are writing these queries you should be able to get some control over the data. If this is something you are doing for work, you may want to let your boss know that you can't guarantee the results.
0
 
MikeWalshCommented:
Well that is not very clean. You don't have any other key? This will cause errors as when people are named there are no laws preventing duplicate names to be issued. Tom is never going to be "LIKE" Thomas unless you are playing with complex logic and soundex and making life difficult.

I think you need to look at another way of handling this, honestly.

0
 
MikeWalshCommented:
You can't really use LIKE in a join either anyway.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Aneesh RetnakaranDatabase AdministratorCommented:
UPDATE Table2
SET Eligible = t2.Eligible
FROM Table1 INNER JOIN Table2 T2
ON T2.Name LIKE Table1.Name+'%'
0
 
MikeWalshCommented:
Aneesh - what is that going to do? That still will not make Tom like Thomas will it? It would work for Ron and Ronald.. What about Mike and Michael? What if there are two Rons in the source and 3 ronalds in the destination?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
MikeWalsh,
> That still will not make Tom like Thomas will it?
You are right,

But I was answering to his Question
  I know you can easily do a join but I don't know how to do it with a "like" involved.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
here is an example


SET NOCOUNT ON
Declare @table1 table (name varchar(32), State varchar(32), Eligible char(1))
Declare @table2 table (name varchar(32), State varchar(32), Eligible char(1))

INSERT INTO @table1 SELECT 'BOB','FL','Y'
INSERT INTO @table1 SELECT 'ROB','FL','Y'
INSERT INTO @table1 SELECT 'Tom','FL','N'


INSERT INTO @table2 SELECT 'Bobby','FL','N'
INSERT INTO @table2 SELECT 'Robert','FL','N'
INSERT INTO @table2 SELECT 'Thomas','FL',NULL
SELECT * from @table2

UPDATE @Table2
SET Eligible = Table1.Eligible
FROM @Table1 Table1 INNER JOIN @Table2 T2
ON T2.Name LIKE Table1.Name+'%'

SELECT * from @table2
0
 
MikeWalshCommented:
Right, but that is not going to be useful in the long run. I think the author needs to use another column to do the comparison on for joins (preferrably a primary key) or there will be nothing but problems in the future.

AJ - Do you have any other columns with key information? How far along are you in your design process? Maybe it is best to redesign those tables to allow for a key column and a primary key - foreign key relationship there.
0
 
ajslentzAuthor Commented:
In the example above I wouldn't expect Tom match Thomas.  Only things that are similar in the way that "bob" and "bobby" might be and "rob" and "robert" might be.  

Yes, the table layout stinks.  Unfortuantely I don't control the data and that's all there really is to work from.  If we could match as described above we'd be happy.  

The following seems reasonable to me.  

UPDATE Table2
SET Eligible = t2.Eligible
FROM Table1 INNER JOIN Table2 T2
ON T2.Name LIKE Table1.Name+'%'

But, could it be modified to:
UPDATE Table2
SET Eligible = t2.Eligible
FROM Table1 INNER JOIN Table2 T2
ON T2.Name LIKE Table1.'%'+Name+'%'
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
A slight change is needed

UPDATE Table2
SET Eligible = T1.Eligible ---------------------- this sould be T1
FROM Table1 T1 INNER JOIN Table2 T2
ON T2.Name LIKE T1.Name+'%'        --- refer the comment from Mike
0
 
ajslentzAuthor Commented:
There are actually ways the data is narrowed so that the "li" to Allison shouldn't happen.  Here's a query that should work if I can get it going.  Any ideas on what's wrong with it?

        StrSQL = "UPDATE X_BHCGSW_TIN INNER JOIN Sheet1 ON " & _
        "X_BHCGSW_TIN_Name_Manual.TIN = Sheet1.TIN " & _
        "SET Sheet1.tin_name = 'Y' WHERE X_BHCGSW_TIN_NAME_Manual.Name " & _
        "LIKE Sheet1.'%' + [Provider Name] + '%'"

If I could get this I'd be set!!!
0
 
ajslentzAuthor Commented:
This is what I finally got to work:

        StrSQL = "UPDATE X_BHCGSW_TIN_Name_Manual INNER JOIN Sheet1 ON " & _
        "[Sheet1].[provider name] " & _
        "LIKE X_BHCGSW_TIN_NAME_Manual.[last_name] + '*'" & _
        "SET Sheet1.tin_name = 'Y' WHERE X_BHCGSW_TIN_NAME_Manual.TIN " & _
        "= Sheet1.TIN"

One last thing...  If I want to do a like on both sides of the [last name] is that possible???, ex:

        StrSQL = "UPDATE X_BHCGSW_TIN_Name_Manual INNER JOIN Sheet1 ON " & _
        "[Sheet1].[provider name] " & _
        "LIKE X_BHCGSW_TIN_NAME_Manual.'*' & [last_name] + '*'" & _
        "SET Sheet1.tin_name = 'Y' WHERE X_BHCGSW_TIN_NAME_Manual.TIN " & _
        "= Sheet1.TIN"
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
ajslentz,
> f I want to do a like on both sides of the [last name] is that possible???
yes , it is also possible..
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
is this what you are looking for

UPDATE @Table2
SET Eligible = Table1.Eligible
FROM @Table1 Table1 INNER JOIN @Table2 T2
ON T2.Name+'%' LIKE Table1.Name+'%'
0
 
ajslentzAuthor Commented:
Actually, I was wanting the % to be on both sides of the table1.name.  So I think it should look like:

UPDATE @Table2
SET Eligible = Table1.Eligible
FROM @Table1 Table1 INNER JOIN @Table2 T2
ON T2.Name LIKE '%' + Table1.Name+'%'


But, I can't get that syntax to work.  Any ideas?  I want to have something like "Billy Bob Smith" match to "bob".  Yes, I know of the accuracy issues but there are other constraints I've not included so I won't be typing all day.

Thanks for the help!!!
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Something like this


SET NOCOUNT ON
Declare @table1 table (name varchar(32), State varchar(32), Eligible char(1))
Declare @table2 table (name varchar(32), State varchar(32), Eligible char(1))

INSERT INTO @table1 SELECT 'BOB','FL','Y'
INSERT INTO @table1 SELECT 'ROB','FL','Y'
INSERT INTO @table1 SELECT 'Tom','FL','N'


INSERT INTO @table2 SELECT 'Bobby','FL','N'
INSERT INTO @table2 SELECT 'Robert','FL','N'
INSERT INTO @table2 SELECT 'Billy Bob Smith','FL',NULL
SELECT * from @table2

UPDATE @Table2
SET Eligible = Table1.Eligible
FROM @Table1 Table1 INNER JOIN @Table2 T2
ON T2.Name LIKE '%'+Table1.Name+'%'

SELECT * from @table2
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.