Query question

I have a query which appears to be looping on it's self. On some of the rows it will return 3 matches but when I query the database there is only one records.  Could you please take a look and let me know why!

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

running32Asked:
Who is Participating?
 
ispalenyConnect With a Mentor Commented:
If you are looking for duplicities by (strLname, strFname) combination
; if the fields are nullable use "SET ANSI_NULLS OFF" command to enable direct NULL comparison. I do not like it, but it can save some time.


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
          FROM tblPatient
          GROUP BY strLname, strFname
          HAVING count(*) > 1) AS NameCount
    on p.strLname = NameCount.strLname
     AND p.strFname = NameCount.strFname
 ORDER BY p.strlname, p.strfname
0
 
ispalenyCommented:
    on p.strLname = NameCount.strLname
     AND p.strFname = NameCount.strFname
    where p.dtmdob = NameCount.dtmdob
    or p.dtmdob is null

     on p.strLname = NameCount.strLname
     AND p.strFname = NameCount.strFname
    AND p.dtmdob = NameCount.dtmdob
    where p.dtmdob is null
0
 
rafranciscoCommented:
Try this one.  If there are still multiple records per patient, make sure that there is only one tblHousehold record for each patient.

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
     AND (p.dtmdob = NameCount.dtmdob or p.dtmdob is null or
             right(p.dtmdob,4) = right(NameCount.dtmdob,4))
ORDER BY p.strlname, p.strfname
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
ispalenyCommented:
1. I think you have a problem with OR conditions. Correct by ANSI syntax for a nullable join is:
ON ( A.Col=B.Col OR (A.Col IS NULL AND B.Col IS NULL) )

2. What are trying to get by condition
right(p.dtmdob,4) = right(NameCount.dtmdob,4)
year????

Then it should be:

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,YEAR(p.dtmdob)  y, Count(*) as Count
          FROM tblPatient
          GROUP BY strLname, strFname, YEAR(p.dtmdob)
          HAVING count(*) > 1) AS NameCount
    on p.strLname = NameCount.strLname
     AND p.strFname = NameCount.strFname
    AND
(
  NameCount.y= YEAR(NameCount.dtmdob)
    or (P.dtmdob is null and NameCount.y is null)
)
ORDER BY p.strlname, p.strfname
0
 
izblankCommented:
If  or p.dtmdob is null, then you join on last  name, first name only, so there will always be multiple matches for those.  Even for non-null dates, there is a remote possibility of multiple matches, so maybe you should just accept this as a fact and do SELECT DISTINCT.  Alternatively, you can do this:

 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
WHERE EXISTS ( SELECT * FROM (
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
     WHERE p.strLname = NameCount.strLname
     AND p.strFname = NameCount.strFname
    AND (p.dtmdob = NameCount.dtmdob
    or right(p.dtmdob,4) = right(NameCount.dtmdob,4)
    or p.dtmdob is null)
)
     --Check for Alias    
ORDER BY p.strlname, p.strfname
0
 
running32Author Commented:
Thanks for all your help.  I am still getting multiples of the exact record with any of these.  tblhousehold only has one record per id so I'm not sure what is going wrong.

Thanks
0
 
rafranciscoCommented:
Try putting a DISTINCT clause in your select.
0
 
rafranciscoCommented:
Also, most probably this part is causing the duplicates:

SELECT strLname, strFname,dtmdob, Count(*) as Count
FROM tblPatient
GROUP BY strLname, strFname, dtmdob
HAVING count(*) > 1

It may be possible that there are patients with the same last name and first name but different dtmdob (date of birth?) records.  Maybe you can just get the maximum dtmdob to make sure that there is just 1 record in this subquery for each last name, first name.
0
 
running32Author Commented:
Hey, thanks for coming back today to help me.

There will be multiple records for the same person but the lngpatientid number is unique so there will only be one person per lngpatientid which is contained in the tblpatient.

I realize I have gone about this incorrectly.  What I need is all people who have the same first and last name could have same birth date, ssn etc......  When they merged the database over they pulled accross so many duplicates.   The tblhousehold does not seem to be duplicated.  It appears but a number of people could be tied to one lnghouseid.

Does this help any?

Thanks
0
 
running32Author Commented:
Maybe I should just start with this query and test from there.

Thanks
0
All Courses

From novice to tech pro — start learning today.