?
Solved

Sql against another table to find Alias

Posted on 2005-04-21
7
Medium Priority
?
224 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:running32
  • 4
  • 2
7 Comments
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 13836101
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
0
 

Author Comment

by:running32
ID: 13836368
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?
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13836604
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
0
Who's Defending Your Organization from Threats?

Protecting against advanced threats requires an IT dream team – a well-oiled machine of people and solutions working together to defend your organization. Download our resource kit today to learn more about the tools you need to build you IT Dream Team!

 

Author Comment

by:running32
ID: 13836935
When I run this I get my results doubled.  
0
 

Author Comment

by:running32
ID: 13836956
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
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13837318
With this code that I added:

    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)

I am checking if the Alias + Last Name for a given patient already exists in the tblPatient and if it does, don't include it in the output.  Is this what you want to do?
0
 

Author Comment

by:running32
ID: 13837654
Yeap, sorry that was me. Thanks for your help it worked great.
0

Featured Post

Evaluating UTMs? Here's what you need to know!

Evaluating a UTM appliance and vendor can prove to be an overwhelming exercise.  How can you make sure that you're getting the security that your organization needs without breaking the bank? Check out our UTM Buyer's Guide for more information on what you should be looking for!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question