INSERT INTO MemberMatch (MemberID,ResidentID,MatchType)
SELECT m.MemberID,c.ResidentID,
CASE WHEN c.DOB=m.DOB AND c.Address<>m.Address THEN 'NameDOBMatch'
     WHEN c.Address=m.Address AND c.DOB<>m.DOB THEN 'NameAddressMatch'
     ELSE 'DoubleMatch'
END
FROM MemberList m
INNER JOIN CityList c
ON m.FirstName=c.FirstName
AND m.LastName=c.LastName
WHERE c.DOB=m.DOB
OR c.Address=m.Address

quote:

---

Originally posted by bwol

Thanks again to both of you for your help.

I created the SQL statement below based on your advice.

You will notice a few changes.

Specifically, some of the table and field names are changed since I tried to use intuitive table and field names in my first post (to avoid any confusion), instead of the actual names (which make sense to me but might be confusing to someone else).

I also made one substantive change. I decided not to compare full addresses to full addresses, since the use of different spacing and abbreviations results in address that are not exactly the same (i.e. not identical strings), but actually are the same address (i.e.
"123 Main Street, Apartment 4, Boston MA 02111" & "123 Main St. Apt#4, Boston MA, 02111).

Accordingly, I decided that an address match occurs when the following three fields, which are included in both the BostonResidents table and the Members Table, all match: StreetName, StreetNumber and Zip.

I have two questions about my updated procedure:

1. Do the changes I have made look correct?

2. The StreetName field in the Members table has values such as "Main" "Washington" & "South", while the StreetName field in the BostonResidents table has values such as "Main St", "Washington Ave" & "South Rd."

It seems to me that in when I am comparing m.StreetName to b.StreetName, I want to be using "like" and "not like" (with a % character) instead of "=" and "<>." However, in the examples I have been able to find, the "like" condition is used to compare something to a string and not to a field. It is possible to use "like" to compare two field names, and if so how?

Thanks again for the help!

-Bryan

Update Procedure

CREATE PROCEDURE dbo.MemberMatch

AS

INSERT INTO MemberBRLMatches (TIMSSID,ResidentID,MatchType)
SELECT m.TIMSSID,b.ResidentID,
CASE
WHEN b.DOB=m.DOB AND (b.StreetNumber<>m.StreetNumber OR b.StreetName<>m.StreetName OR b.Zip<>m.Zip) THEN 'NameDOBMatch'
WHEN (b.StreetNumber=m.StreetNumber AND b.StreetName=m.StreetName AND b.Zip=m.Zip) AND b.DOB<>m.DOB THEN 'NameAddressMatch'
ELSE 'DoubleMatch'
END
FROM Members m
INNER JOIN BostonResidents b
ON m.FirstName=b.FirstName
AND m.LastName=b.LastName
WHERE b.DOB=m.DOB AND m.TIMSSID IN ('BO017658', 'BO017658', 'BO017658')
OR (b.StreetNumber=m.StreetNumber AND b.StreetName=m.StreetName AND b.Zip=m.Zip)

---