SQL NOT IN to return only if all values ​​are not set

Question

I am trying to write a request that will allow me to search if the patient has NOT received the medicine. At first I thought I could just do something like this:

INNER JOIN prescript p ON p.id = patient.id AND p.med_id NOT IN (5128) 

Where p.id and patient.id are the patient identifier, p.med_id refers to the medication identifier, and 5128 is the medication used in the request. The problem with this question is that if the patient had other medicines, they will appear. Therefore, if the patient has medicine 5128, but also another medicine that they will find. But I only want to return the patient if they do not have this medicine at all. The part that is complicated here is that I will not be able to find out the patient identifier in any of these cases. So I need a solution like this:

 INNER JOIN prescript p ON p.id = patient.id AND (SELECT med_id FROM prescript WHERE id = p.pid) NOT IN (5128) 

I thought it was possible if I could combine all med_id into one line, but my problem is that I use the query builder, so I am limited to adding joins and adding wheres only. Aka I can not use DECLARE .

Answer 1

You can use the NOT EXISTS (SELECT * FROM prescript WHERE id = patient.id AND med_id = 5128) in the WHERE statement.

If you need to exclude a patient who has used at least one of several drugs, you can use med_id IN (5128, ...) instead.

Answer 2

All patients who did not have 5128 drugs ...

 Select patient.* from patient left join prescript on patient.id = prescript.patientid and prescript.medid = 5128 where prescript.id is null 

All patients who did not have 5128 or 3000 medications.

 Select patient.* from patient left join prescript on patient.id = prescript.patientid and prescript.medid in ( 5128 ,3000) where prescript.id is null 

All patients who did not have medicines 5128 and 3000

 Select patient.* from patient left join prescript on patient.id = prescript.patientid and prescript.medid in ( 5128 ,3000) group by patient.id, patient.name having COUNT(distinct medid)<>2 

Answer 3

Try left join :

 left join prescript p on p.id = patient.id and med_id = 5128 where p.id is null