asked on

Delete unwanted rows

I have a table "TreatmentData" defined as:

create table #TreatmentData(

PatientId nvarchar(18),
TreatedWith nvarchar(30) );

The table lists patient treatments. A patient can have many rows in the table. There are six possible values for TreatedWith.

I need to delete all the patient records where the patient has "NoTreatment" for all his/her rows in the database. i.e. they have visited the clinic but not been treated.

I imagine it must be where the number of rows is equal to the count where the TreatedWith is "NoTreatment" when grouped by PatientId...

but i just can not get my head round it.

Any suggestions?

SOLUTION

Surendra Nath

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DOSLover

Essentially what we are saying here is that delete the patient record if we find only 'NoTreatment' records for that patient:

Delete from TreatmentData a
 where exists 
       (Select PatientId from TreatmentData b
	     where b.PatientId = a.PatientId
		   and b.TreatedWith = 'NoTreatment')
   and NOT exists 
       (Select PatientId from TreatmentData b
	     where b.PatientId = a.PatientId
		   and b.TreatedWith <> 'NoTreatment')

Open in new window

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chaau

Just kidding: when you apply all the SQL statements against you treatment table that you have asked during last week, you will probably end up with a nice empty table. Ha-ha

awking00

delete from treatmentdata
where patientid in
(select patientid from treatmentdata where treatedwith = 'NoTreatment'
except
select patientid from treatmentdata where treatedwith <> 'NoTreatment')