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