SELECT *
FROM BPS_Patients p
LEFT JOIN 
    (
     SELECT InternalId
     ,COUNT(*) AS Count
     FROM Immunisations
     WHERE VACCINEID IN 
        (
         SELECT VACCINEID
         FROM BPSDrugs..VACCINE_DISEASE v_d
         INNER JOIN BPSDrugs..VAXDISEASES vd ON v_d.DISEASECODE = vd.DISEASECODE
         WHERE 1 = 1
         AND vd.DiseaseCode = 34
        )
     GROUP BY InternalID
    ) imm ON imm.InternalID = p.InternalID
WHERE StatusText = 'Active'

--AND (imm.Count IS NULL OR imm.Count < 4) --Patients who have had less than or no COVID Imms.
AND imm.Count < 4 --Patients who have had less than 4 COVID Imms.

AND p.Ethnicity NOT LIKE '%Australian%' --Add Patient Ethnicity between %'s.

ORDER BY p.Surname, p.FirstName;
--List of patients by count of COVID Vaccines received.