asked on

Sql against another table to find Alias

I have a query to try and locate the duplicate records in a database (below). I also have a table with a list of Alias for the first name (ie Alley = Allison) When I would like to do is query this table in the query below ***** to see if the fname matches any of alias to see if I have a first name last name match.

thanks for your help

SELECT P.strPatientid, P.strLname, P.strmname, P.strFname,
convert(char(10),P.dtmDOB, 101) As DOB,
P.strSSNum, P.strPrgEntry,NameCount.Count, H.strAddress, H.StrCity, H.strState
FROM tblPatient as P
INNER JOIN tblHousehold As H ON p.lngHouseID = h.lngHouseID
INNER JOIN (SELECT strLname, strFname,dtmdob, Count(*) as Count
FROM tblPatient
GROUP BY strLname, strFname, dtmdob
HAVING count(*) > 1) AS NameCount
--If lname and fname are duplicated
on p.strLname = NameCount.strLname
AND p.strFname = NameCount.strFname
where p.dtmdob = NameCount.dtmdob
or p.dtmdob is null
or right(p.dtmdob,4) = right(NameCount.dtmdob,4)
or p.dtmdob is null
--Check for Alias ******************************
ORDER BY p.strlname, p.strfname

Brendt Hess

Assuming an alias table like

BaseName, Alias

Create table #AllAliasNames (PatientID int not null, strLName varchar(20), strFName varchar(20), dtmDOB datetime)

Insert Into #AllAliasNames
SELECT P.strPatientid, P.strLname, P.strFname, P.dtmDOB
FROM tblPatient as P

--- Now, add in aliases for those names with aliases
Insert Into #AllAliasNames
Select p.PatientID, p.StrLName, a.strAlias, p.dtmDOB
FROM tblPatient as P
Inner Join tblAlias as a ON p.FName = a.BaseName

--- Index temp table for speed - make it all fields but patientID
Create Index ix1 ON #AllAliasNames strLName, strFName, dtmDOB

--- Now, look for duplicates
SELECT a1.PatientID, a2.PatientID
FROM #AllAliasNames a1
INNER JOIN #AllAliasNames a2
ON a1.strLName = a2.strLName
AND a1.strFName = a2.strFNAME
AND a1.dtmDOB = a2.dtmDOB
WHERE a1.PatientID < a2.PatientID --- don't allow duplicates of same set of values

The returned recordset will be pairs of PatientIDs that are duplicates (potentially) by this matching pattern

running32

ASKER

Thanks for attempting to help me!

I have a table name Alias and you are correct I have Name and then Alias name within this table. How do I put your code into the query I have above. Can I join the tables and then compare the base name with the alias name?

rafrancisco

Try this one:

SELECT P.strPatientid, P.strLname, P.strmname, P.strFname,
convert(char(10),P.dtmDOB, 101) As DOB,
P.strSSNum, P.strPrgEntry,NameCount.Count, H.strAddress, H.StrCity, H.strState
FROM tblPatient as P
INNER JOIN tblHousehold As H ON p.lngHouseID = h.lngHouseID
INNER JOIN (SELECT strLname, strFname,dtmdob, Count(*) as Count
FROM tblPatient
GROUP BY strLname, strFname, dtmdob
HAVING count(*) > 1) AS NameCount
--If lname and fname are duplicated
on p.strLname = NameCount.strLname
AND p.strFname = NameCount.strFname

where (p.dtmdob = NameCount.dtmdob
or p.dtmdob is null
or right(p.dtmdob,4) = right(NameCount.dtmdob,4)
or p.dtmdob is null)
AND NOT EXISTS (SELECT 'X' FROM tblPatient P2 INNER JOIN Alias A -- This is the start of what I added
ON P2.strFName = A.Alias AND
P2.strLName = P.strLName AND
A.Name = P.strFName)
--Check for Alias ******************************
ORDER BY p.strlname, p.strfname

running32

ASKER

When I run this I get my results doubled.

running32

ASKER

Sorry hit submit to quickly.

It appears to be just getting the matches of first and last name which I am already doing. Should I only be checking on Alias.

thanks

ASKER CERTIFIED SOLUTION

rafrancisco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

running32

ASKER

Yeap, sorry that was me. Thanks for your help it worked great.