CODEWITHSUNDEEP
sqlsql-server
pQuestions123
pQuestions123

Reputation: 4611

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

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?

Upvotes: 1

Views: 2349

Answers (5)

Dudi Konfino
Dudi Konfino

Reputation: 1136

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 ()

Upvotes: 0

Whirl Mind
Whirl Mind

Reputation: 884

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)

Upvotes: 0

Red
Red

Reputation: 858

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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.

Upvotes: 1

simon_dmorias
simon_dmorias

Reputation: 2473

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

Upvotes: 1

Related Questions