Delete unwanted rows
Posted on 2013-06-15
I have a table "TreatmentData" defined as:
create table #TreatmentData(
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.