How do I Join on only part of a matching column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How do I Join on only part of a matching column

Author

Topic

hearnie
Starting Member

49 Posts

Posted - 2005-07-13 : 10:39:55

Im basically trying to update a table based on values in 2 other tables, but one of the criteria has a name. In the first table, the 'name' could have only 7 characters and in the second table it could have 8. How do I join on just the first 7 characters

UPDATE A SET A.name = B.name
FROM table1 A
LEFT JOIN table3 C on A.phone = C.phone
LEFT JOIN table2 B on B.surname = C.surname
where A.name = '' and B.name <> ''

So the part I am trying to achieve is that
In table B surname can have only 7 characters
While in table C it can have 8
So I dont want to have B.surname = C.surname
I just want to match on the first 7 chars......

Help please????

H.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-13 : 10:56:14

So Do you want to compare with first 7 charaters of C.Name?
If so

UPDATE A SET A.name = B.name
FROM table1 A
LEFT JOIN table3 C on A.phone = C.phone
LEFT JOIN table2 B on B.surname = Left(C.surname,7)
where A.name = '' and B.name <> ''

Madhivanan

Failing to plan is Planning to fail

HCLollo
Starting Member

49 Posts

Posted - 2005-07-13 : 11:02:25

Mmm...was thinking of

B.surname LIKE C.surname + '%'

but probably Madhivanan's is much more versatile

HCL

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-13 : 11:48:36

HCLollo -- yours is the way to go; it can use indexes and is shorter and more efficient.

- Jeff

hearnie
Starting Member

49 Posts

Posted - 2005-07-13 : 11:48:42

thanks a million, LEFT() worked a treat.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-07-13 : 15:45:00

>>>B.surname LIKE C.surname + '%'
<<<

Since C.Surname is longer than B.Surname, would this logic yield the proper results?

Ex: "Apple" LIKE "Apples" + '%'

HTH

=================================================================
The most tyrannical of governments are those which make crimes of opinions, for everyone has an inalienable right to his thoughts. -Baruch Spinoza, philosopher (1632-1677)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-13 : 16:05:06

yeah, s/b the other way around ....

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources