where exists with inclusion and exclusion statements

user_12549160 0
Viewed 39

I have the following data table:

patient_id    disease
hashA         HIV-2
hashA         COPD-1
hashA         diabetes
hashB         diabetes
hashB         FSGS
.
.
.

I would like to extract all the rows for patients who have diabetes, but not COPD or HIV.

So far, I have:

select t.*
from patient_table t
   where exists (select 1
      from patient_table t2
      where t2.disease like '%diabetes%' and
            t2.disease not like '%HIV%' and 
            t2.disease not like '%COPD%' and
            t.patient_id = t2.patient_id
)

This however does not exclude all the rows for patients with COPD and HIV. Is there an alternative?

mysql sql
4 Answers

Set the diseases that you don't want in an separate not exists

The code woud exluce pationet that has either copd or hiv

Edit: ia dded a code of you need both other deseases excludes

CREATE TABLE patient_table
    (`patient_id` varchar(5), `disease` varchar(8))
;
    
INSERT INTO patient_table
    (`patient_id`, `disease`)
VALUES
    ('hashA', 'HIV-2'),
    ('hashA', 'COPD-1'),
    ('hashA', 'diabetes'),
    ('hashB', 'diabetes'),
    ('hashB', 'FSGS')
;

select t.*
from patient_table t
   where exists (select 1
      from patient_table t2
      where t2.disease like '%diabetes%'  and
            t.patient_id = t2.patient_id
)
AND NOT EXISTS (select 1
      from patient_table t2
      where
            (t2.disease  like '%HIV%' OR 
            t2.disease  like '%COPD%') and
            t.patient_id = t2.patient_id)

patient_id | disease 
:--------- | :-------
hashB      | diabetes
hashB      | FSGS    

select t.*
from patient_table t
   where exists (select 1
      from patient_table t2
      where t2.disease like '%diabetes%'  and
            t.patient_id = t2.patient_id
)
AND (
        select count(patient_id)
        from patient_table 
        where (disease  like '%HIV%' OR 
            disease  like '%COPD%') AND patient_id = t.patient_id
)<2

patient_id | disease 
:--------- | :-------
hashB      | diabetes
hashB      | FSGS

db<>fiddle here

user_51935360

If you are (hopefully) using MySql 8 you can use window functions with a conditional sum

with cte as (
    select *, Sum(case when disease like '%HIV%' or disease like '%COPD%' then 1 else 0 end) over(partition by patient_id) v
    from t
)
select patient_id, disease
from cte
where v=0

Example Fiddle

user_153326500 
select t.*
from patient_table t
   where exists (select 1
      from patient_table t2
      where t2.disease like '%diabetes%')
user_141222460

Look at this, might be helpful:

SELECT patient_table.* 
FROM patient_table
LEFT JOIN patient_table AS not_allowed_1 
    ON not_allowed_1.patient_id = patient_table.patient_id AND not_allowed_1.disease like '%HIV%'
LEFT JOIN patient_table AS not_allowed_2 
    ON not_allowed_2.patient_id = patient_table.patient_id AND not_allowed_2.disease like '%COPD%'
WHERE patient_table.disease like '%diabetes%' 
    AND not_allowed_1.patient_id IS NULL 
    AND not_allowed_2.patient_id IS NULL

It will left join each row which disease like '%diabetes%' twice with same table on rows with disease like '%COPD%' and disease like '%HIV%' then filters row which left joined rows has a value.

Joining is what SQL deeply know how to handle and this is way too much faster than EXISTS operator.

user_164841000
Related