?
Solved

How to join with a like

Posted on 2006-05-31
18
Medium Priority
?
269 Views
Last Modified: 2008-03-10
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"))
0
Comment
Question by:ajslentz
  • 7
  • 5
  • 4
16 Comments
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16799783
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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16799789
You can't really use LIKE in a join either anyway.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16799792
UPDATE Table2
SET Eligible = t2.Eligible
FROM Table1 INNER JOIN Table2 T2
ON T2.Name LIKE Table1.Name+'%'
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16799828
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16799881
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16799884
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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16800143
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
 

Author Comment

by:ajslentz
ID: 16800380
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
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 500 total points
ID: 16800557
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16800650
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
 

Author Comment

by:ajslentz
ID: 16800697
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
 

Author Comment

by:ajslentz
ID: 16800928
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16800967
ajslentz,
> f I want to do a like on both sides of the [last name] is that possible???
yes , it is also possible..
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16800982
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
 

Author Comment

by:ajslentz
ID: 16801752
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
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 500 total points
ID: 16804937
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question