April 22, 2005 at 9:59 am
Good morning,
I have two tables, Patient_Policies and Vouchers.
Patient_Policies has a field Patient_Policy_ID
Vouchers has Current_Patient_Policy_ID and Original_Patient_Policy_ID
I would like to delete Patient_Policies records if the Patient_Policy_ID does not exist in Vouchers in either Current or Original.
If I only had to worry about one, say Current_Patient_Policy_ID I would do this:
DELETE Patient_Policies WHERE Patient_Policy_ID IN
(SELECT pp.Patient_Policy_ID FROM Patient_Policies pp LEFT JOIN
Vouchers v ON pp.Patient_Policy_ID = v.Current_Patient_Policy_ID WHERE v.Current_Patient_Policy_ID IS NULL)
I can't quite get how to do when the Patient_Policy_ID can't be in Current or Original?
Thank you!
jmatt
April 22, 2005 at 10:33 am
DELETE FROM
Patient_Policies
WHERE
Patient_Policy_ID NOT IN ( SELECT DISTINCT Current_Patient_Policy_ID FROM Vouchers)
AND Patient_Policy_ID NOT IN ( SELECT DISTINCT Original_Patient_Policy_ID FROM Vouchers)
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
April 22, 2005 at 10:49 am
you could also do a left join
delete p
from patient_policies p
left join vouchers v
on p.patient_policy_id = v.current_patient_policy_id
and p.patient_policy_id = v.original_patient_policy_id
where ((v.current_patient_policy_id is null) and (v.original_patient_policy_id is null))
April 22, 2005 at 11:37 am
Thank you Dinakar and Seth!
jmatt
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply