One query that matches values with only one condition out of two, one query that matches values with-CodePudding

I'm having some sort of a blank about how to do this in SQL.

Consider this reprex in R

set.seed(123)
data.frame(ID = (sample(c(1:5), 10, replace = T)),
       status = (sample(c("yes", "no"), 10, replace = T)),
       amount = (sample(seq(1,50,0.01),10)))

which gives out this table

   ID status amount
1   3     no  29.87
2   3    yes  26.66
3   2    yes  15.49
4   2    yes  18.89
5   3    yes  44.06
6   5     no  30.79
7   4    yes  17.13
8   1    yes   6.54
9   2    yes  45.68
10  3    yes  12.66

I need to find two SQL queries.

One where I select the ID's that only have status of 'NO' meaning ID 5.

and

One where I select the ID's that match both conditions, meaning ID 3

I have a query for both but I'm almost sure it's not correct so any lead is more than welcome.

Thanks

CodePudding user response：

One where I select the ID's that only have status of 'NO' meaning ID 5.

select id from your_table where status='no' and id not in (select id from 
your_table  where status='yes')

One where I select the ID's that match both conditions, meaning ID 3

select id from your_table where status='no' and id  in (select id from 
your_table where status='yes')

At last I think you are expecting ids which do not match these conditions. so UNION both queries and get ids of your table which not exists after UNION

select id from your_table where id not in (
    select id from your_table where status='no' and id not in 
    (select id from your_table where status='yes')
    union all
    select id from your_table where status='no' and id  in 
    (select id from your_table where status='yes')
)