How to check for combination of column values in sql server table

Question

The following code is throwing an error:

select * 
from inPersonMedChangeNotes as IP
where 
    (IP.[Date], IP.Patient) not in (select EncounterDate, Patient 
                                    from tbl_patients_clinic_visit_records as VC
                                    join tbl_patients as PS on VC.PatientId = PS.PatientId_this)

Error:

non boolean expression where condition is expected

I am trying to find all the entries in inPersonMedChangeNotes where the combination of values is not in the tbl_patients_clinic_visit_records table. How do I do this?

You cannot use multiple columns for your `not in` clause.. – SoulTrain Feb 05 '15 at 16:55 — SoulTrain, Feb 05 '15 at 16:55

score 1 · Accepted Answer · answered Feb 05 '15 at 16:45

1

You can do this with a left join:

SELECT * 
FROM inPersonMedChangeNotes as IP
LEFT JOIN tbl_patients_clinic_visit_records as VC ON IP.[Date] = VC.EncounterDate AND IP.Patient = VC.Patient
left join tbl_patients as PS on VC.PatientId = PS.PatientId_this
WHERE VC.EncounterDate IS NULL

answered Feb 05 '15 at 16:45

simon_dmorias

2,343
3
19
33

except you would probably want to limit the select to IP.* to match the desired output of the request – randcd Feb 05 '15 at 16:48
Here is what I ended up using: SELECT * FROM inPersonMedChangeNotes as IP LEFT JOIN tbl_patients_clinic_visit_records as VC ON IP.[Date] = VC.EncounterDate left join tbl_patients as PS on VC.PatientId = PS.PatientId_this AND IP.Patient = PS.Name WHERE PatientClinicVisitRecordsId_this is null and PatientId_this is null – pQuestions123 Feb 05 '15 at 17:03

score 1 · Answer 2 · answered Feb 05 '15 at 16:49

1

Following the same structure of your query, you can use not exists:

select * 
from inPersonMedChangeNotes IP
where not exists (select 1
                  from tbl_patients_clinic_visit_records
                  where ip.patient = vc.patientid and
                        ip.[date] = vc.encounterdate
                 );

I don't think the patient table is needed for the query.

answered Feb 05 '15 at 16:49

Gordon Linoff

1,242,037
58
646
786

patient table is needed because ip.patient is a string and vc.patientid is an int – pQuestions123 Feb 05 '15 at 17:04

score 0 · Answer 3 · edited May 23 '17 at 11:57

You need to use LEFT OUTER JOIN for that. In your case (untested):

select * 
from inPersonMedChangeNotes as IP
LEFT JOIN (
          select EncounterDate, Patient 
          from tbl_patients_clinic_visit_records as VC 
          inner join tbl_patients as PS on VC.PatientId = PS.PatientId_this
) V ON V.EncounterDate = IP.[Date] and IP.Patient = V.Patient 
where v.EncounterDate IS NULL

score 0 · Answer 4 · answered Feb 05 '15 at 16:53

The NOT IN query can check against only a single field, it cannot check against multiple fields at the same time. Check for each condition in a separate NOT IN clause and use an AND between them. Like this :

select * 
from inPersonMedChangeNotes as IP
where 
    IP.[Date] not in (select EncounterDate
                                    from tbl_patients_clinic_visit_records as VC
                                    join tbl_patients as PS on VC.PatientId = PS.PatientId_this)

And 


    IP.Patient not in (select Patient 
                                    from tbl_patients_clinic_visit_records as VD
                                    join tbl_patients as PQ on VD.PatientId = PQ.PatientId_this)

I don't think this will accomplish what I am trying to accomplish — pQuestions123, Feb 05 '15 at 17:02

Dudi Konfino · Answer 5 · 2015-02-05T17:49:11.400

0

First create function that will return EncounterDate, Patient then use outer apply. (if it works -:) ) it will give you best performance.

CREATE function spn ()
 RETURNS TABLE 
AS 
RETURN
    (select EncounterDate, Patient 
          from tbl_patients_clinic_visit_records as VC
          join tbl_patients as PS on VC.PatientId = PS.PatientId_this)
         GO

    select *
    from inPersonMedChangeNotes as IP
    OUTER APPLY dbo.spn ()

edited Feb 05 '15 at 17:49

answered Feb 05 '15 at 17:14

Dudi Konfino

1,126
2
13
24

you can also create trigger on database for alter table to track any columns change – Dudi Konfino Feb 05 '15 at 18:53

How to check for combination of column values in sql server table

5 Answers5