dmanisit
asked on
SQL Query to detect Dups
Hi all,
I have several tables that need joined and duplicate records detected. (NOT DELETED). So here is the Layout;
Table Contacts has a fields I need to match on;
Field 1 = Last_Name
Field 2 = First_Name
Field 3 = Date_Of_Birth
Also in this table needs joined to Table Patients with these 2 fields are = and they are Contact_ID from the Contacts table joined to Patients Table Field = Patient_ID. These are the same in both tables.
Now from the Patients Table, I need 1 Field called End_Date. THIS is the PRIMARY field that this Query needs to revolve around with the above data.
So here is the key. If the End_Date is NULL that means that the record is ACTIVE in the Database, if it is NOT NULL then that record is already marked inactive. MEANING I dont care if I have duplicates with 1 record marked Inactive and the other record is Active.
ALSO, last piece to the Puzzle, I need another JOIN from Contacts Table, field will be again a 1 to 1 where the field Contact_ID in the Contacts Table = Policies Table field is also Contact_ID.
Thank you very much, Im new to SQL and this one is driving me crazy
I have several tables that need joined and duplicate records detected. (NOT DELETED). So here is the Layout;
Table Contacts has a fields I need to match on;
Field 1 = Last_Name
Field 2 = First_Name
Field 3 = Date_Of_Birth
Also in this table needs joined to Table Patients with these 2 fields are = and they are Contact_ID from the Contacts table joined to Patients Table Field = Patient_ID. These are the same in both tables.
Now from the Patients Table, I need 1 Field called End_Date. THIS is the PRIMARY field that this Query needs to revolve around with the above data.
So here is the key. If the End_Date is NULL that means that the record is ACTIVE in the Database, if it is NOT NULL then that record is already marked inactive. MEANING I dont care if I have duplicates with 1 record marked Inactive and the other record is Active.
ALSO, last piece to the Puzzle, I need another JOIN from Contacts Table, field will be again a 1 to 1 where the field Contact_ID in the Contacts Table = Policies Table field is also Contact_ID.
Thank you very much, Im new to SQL and this one is driving me crazy
Jesus Rodriguez
Can you post the table Structure??
Will Be something like this more or less
SELECT C.LAST_NAME
,C.FIRST_NAME
,C.DATE_OF_BITRTH
,P.END_DATE
,O.POLICIES_DESC
FROM CONTACTS as C
INNER JOIN PATIENS as P
ON P.PATIENTID=C.PATIENT_ID
LEFT JOIN POLICIES AS O
ON O.CONTACT_ID=C.CONTACT_ID
WHERE P.END_DATE IS NOT NULL
THe fields that you want from each table can be extracted on the select with the letter that is assign toi the table and the field for example
From Policies - Description of the policies
O.POLICIES_DESCRIPT
SELECT C.LAST_NAME
,C.FIRST_NAME
,C.DATE_OF_BITRTH
,P.END_DATE
,O.POLICIES_DESC
FROM CONTACTS as C
INNER JOIN PATIENS as P
ON P.PATIENTID=C.PATIENT_ID
LEFT JOIN POLICIES AS O
ON O.CONTACT_ID=C.CONTACT_ID
WHERE P.END_DATE IS NOT NULL
THe fields that you want from each table can be extracted on the select with the letter that is assign toi the table and the field for example
From Policies - Description of the policies
O.POLICIES_DESCRIPT
ASKER
ASKER
So as of thus far, to get your script to work, this is the changes I have made
SELECT C.Last_Name ,C.First_Name ,C.Date_of_Birth, P.End_Date
From pm.Contacts as C
INNER JOIN pm.Patients as P
ON P.Patient_ID = C.Contact_ID
LEFT JOIN pm.Policies AS O
ON O.Contact_ID = C.Contact_ID
WHERE P.END_DATE IS NOT NULL
SELECT C.Last_Name ,C.First_Name ,C.Date_of_Birth, P.End_Date
From pm.Contacts as C
INNER JOIN pm.Patients as P
ON P.Patient_ID = C.Contact_ID
LEFT JOIN pm.Policies AS O
ON O.Contact_ID = C.Contact_ID
WHERE P.END_DATE IS NOT NULL
Yeap, that's the correct one
ASKER
So the script is pulling ALL Patients that are inactive due to the last line of code.
WHERE P.END_DATE IS NOT NULL
But heres what I need;
I need to only FIND the Patients that have multiple records but thats conditional for example if there are 2 john smiths and 1 of those records Have an End_Date, then I DONT want to display this guy at all. I ONLY want when there are 2 Mary Janes and End_Date = NULL to display those
WHERE P.END_DATE IS NOT NULL
But heres what I need;
I need to only FIND the Patients that have multiple records but thats conditional for example if there are 2 john smiths and 1 of those records Have an End_Date, then I DONT want to display this guy at all. I ONLY want when there are 2 Mary Janes and End_Date = NULL to display those
ASKER
What do you think about a Count(*) column somehow??????
You can move the condition to the top like this
SELECT C.Last_Name
,C.First_Name
,C.Date_of_Birth
,P.End_Date
From pm.Contacts as C
INNER JOIN
(SELECT PATIENS_ID,End_Date
FROM pm.Patients
WHERE END_DATE IS NOT NULL) as P
ON P.Patient_ID = C.Contact_ID
LEFT JOIN pm.Policies AS O
ON O.Contact_ID = C.Contact_ID
SELECT C.Last_Name
,C.First_Name
,C.Date_of_Birth
,P.End_Date
From pm.Contacts as C
INNER JOIN
(SELECT PATIENS_ID,End_Date
FROM pm.Patients
WHERE END_DATE IS NOT NULL) as P
ON P.Patient_ID = C.Contact_ID
LEFT JOIN pm.Policies AS O
ON O.Contact_ID = C.Contact_ID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you very much