> SQL 2000, I'll look into CASE and see if it'll do what I need.
OK, Here is an example of what will work, but may or may not be faster.
My tables are different so you will have to tweek them to match yours.
The >= 2 is how many matches you want, if you want just one match it
would be 1, 3 for 3, etc.
Let me know if this is faster. My idea is that since you are only doing
one join it could possibly speed things up, but who knows until you try it.
SELECT M.FirstName AS mFirstName,
M.MiddleName AS mMiddleName,
M.LastName AS mLastName,
MC.FirstName AS mcFirstName,
MC.MiddleName AS mcMiddleName,
MC.LastName AS mcLastName,
CASE WHEN M.FirstName = MC.LastName
OR M.FirstName = MC.MiddleName
OR M.FirstName = MC.FirstName
THEN 1 ELSE 0
END +
CASE WHEN M.LastName = MC.FirstName
OR M.LastName = MC.MiddleName
OR M.LastName = MC.LastName
THEN 1 ELSE 0
END +
CASE WHEN M.MiddleName = MC.LastName
OR M.MiddleName = MC.FirstName
OR M.MiddleName = MC.MiddleName
THEN 1 ELSE 0
END AS MatchCount
FROM Member M,
MemberCompare MC
WHERE
CASE WHEN M.FirstName = MC.LastName
OR M.FirstName = MC.MiddleName
OR M.FirstName = MC.FirstName
THEN 1 ELSE 0
END +
CASE WHEN M.LastName = MC.FirstName
OR M.LastName = MC.MiddleName
OR M.LastName = MC.LastName
THEN 1 ELSE 0
END +
CASE WHEN M.MiddleName = MC.LastName
OR M.MiddleName = MC.FirstName
OR M.MiddleName = MC.MiddleName
THEN 1 ELSE 0
END
>= 2
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
