Try runnng this:
CREATE TABLE #table1 (
LName VARCHAR(100),
FName VARCHAR(100),
City VARCHAR(100)
)
CREATE TABLE #table2 (
LName VARCHAR(100),
FName VARCHAR(100),
City VARCHAR(100)
)
INSERT INTO #table1 ( LName, FName, City ) VALUES ( 'DOE JR', 'JOHN',
'PHOENIX' )
INSERT INTO #table1 ( LName, FName, City ) VALUES ( 'GATES', 'BILL',
'REDMOND' )
INSERT INTO #table1 ( LName, FName, City ) VALUES ( 'GORE III', 'AL',
'BACKWOODS' )
INSERT INTO #table1 ( LName, FName, City ) VALUES ( 'BRADSHAW', 'TERRY',
'HOUSTON' )
INSERT INTO #table2 ( LName, FName, City ) VALUES ( 'DOE', 'JOHN',
'PHOENIX' )
INSERT INTO #table2 ( LName, FName, City ) VALUES ( 'GATES', 'BILL',
'REDMOND' )
INSERT INTO #table2 ( LName, FName, City ) VALUES ( 'GORE', 'AL',
'BACKWOODS' )
INSERT INTO #table2 ( LName, FName, City ) VALUES ( 'BRADSAW', 'TERRY',
'HOUSTON' )
SELECT t1.*, t2.*
FROM #table1 t1
INNER JOIN #table2 t2 ON (
t1.LName = t2.LName OR SUBSTRING(t1.LName, 1, CHARINDEX(' ', t1.LName))
LIKE t2.LName
)
It looks like it's doing the right thing, joining on last name in spite or
the suffix.
Ade
-----Original Message-----
From: Eric Creese [mailto:[EMAIL PROTECTED]
Sent: 23 February 2005 22:58
To: CF-Talk
Subject: RE: OT-SQL matching
hmmmmm not sure unless I give it a try.
Here is my revised SQL, I got rid of the substring on the last name and went
with matching the first three letters
SELECT
n.[last name] as lastname,x.perlname,
n.[first name] as firstname,x.perfname,
n.[street 1],x.addrline1,n.city,n.state ,x.city,x.state
FROM NEW_STUDENTS n, EAA_CHECK x
WHERE LEFT(n.[first name],1) = left(x.perfname,1)
AND n.city =x.city
AND n.state = x.state
AND LEFT(n.[street 1],3)= left(x.addrline1, 3)
AND LEFT(n.[last name],3) = left(x.perlname,3)
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 266.4.0 - Release Date: 22/02/2005
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:196235
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
