Fat Budapest Fat Budapest - 4 months ago 11
SQL Question

Need to select only patients who have not specific codes

I have here an issue in this sql - it will give me the rows that are not these diagnosis codes. But a patient can have them as well.

I need patients who do not have these 3 codes

V72.31', 'Z01.411', 'Z01.419' at all.

SELECT distinct "Vouchers"."Patient_ID"
FROM (("Ntier_70751"."PM"."Service_Diagnoses" "Service_Diagnoses"
INNER JOIN "Ntier_70751"."PM"."Services" "Services"
ON "Service_Diagnoses"."Service_ID"="Services"."Service_ID")
INNER JOIN "Ntier_70751"."PM"."Diagnosis_Codes" "Diagnosis_Codes"
ON "Service_Diagnoses"."Diagnosis_Code_ID"="Diagnosis_Codes"."Diagnosis_Code_ID")
INNER JOIN "Ntier_70751"."PM"."Vouchers" "Vouchers" ON "Services"."Voucher_ID"="Vouchers"."Voucher_ID"
WHERE "Diagnosis_Codes"."Diagnosis_Code" not in ('V72.31', 'Z01.411', 'Z01.419')

Answer

Your desired outcome is unclear.

This will show patients who have your "invalid" codes, so long as they have at least one code that is not in your exclusion list:

SELECT DISTINCT
    v.Patient_ID 
FROM
    Ntier_70751.PM.Service_Diagnoses sd

    INNER JOIN Ntier_70751.PM.Services s 
    ON sd.Service_ID = s.Service_ID

    INNER JOIN Ntier_70751.PM.Diagnosis_Codes dc 
    ON sd.Diagnosis_Code_ID = dc.Diagnosis_Code_ID 
    AND dc.Diagnosis_Code not in ('V72.31', 'Z01.411', 'Z01.419') 

    INNER JOIN Ntier_70751.PM.Vouchers v 
    ON s.Voucher_ID= v.Voucher_ID

And this one will exclude patients who have at least one of the "invalid" codes (regardless of what other "valid" codes they may have):

SELECT DISTINCT
    v.Patient_ID
FROM
    Ntier_70751.PM.Vouchers v 
WHERE
    v.Patient_ID NOT IN

(
    SELECT DISTINCT
        v.Patient_ID 
    FROM
        Ntier_70751.PM.Service_Diagnoses sd

        INNER JOIN Ntier_70751.PM.Services s 
        ON sd.Service_ID = s.Service_ID

        INNER JOIN Ntier_70751.PM.Diagnosis_Codes dc 
        ON sd.Diagnosis_Code_ID = dc.Diagnosis_Code_ID 
        AND dc.Diagnosis_Code in ('V72.31', 'Z01.411', 'Z01.419') 

        INNER JOIN Ntier_70751.PM.Vouchers v 
        ON s.Voucher_ID= v.Voucher_ID
)