How to extract values from column that have only one specific value in other column with SQL?-CodePudding

I have a table:

id       value     type
a1       146       out
a1       198       upload
bb       11        upload
q5       16        upload
q5       88        upload
ff       19        delete

I want to extract unique id's that have only type equal "upload". So desired must be:

bb
q5

How to do that?

CodePudding user response：

First and most common approach would be to use "not exists"

select distinct td.id
  from test_data td
 where td.type = 'upload'
   and not exists (select 1 
                     from test_data td2 
                    where td2.id = td.id 
                      and td2.type <> 'upload');

But I like to use left join for that case as it helps to avoid subqueries

select distinct td1.id 
  from test_data td1
  left join test_data td2
    on td1.id = td2.id
    and td2.type <> 'upload'
 where td1.type = 'upload'
   and td2.id is null

the third way would be more tricky but let it be here too

first, select those with that has only one type (having clause) then, self-join on the id filtering only those who has type = 'upload'

select td.id
  from test_data td
  join test_data td2
    on td.id = td2.id
   and td2.type = 'upload'
 group by td.id
having count(distinct td.type) = 1

Just pick the one which performs faster

CodePudding user response：

You can try below query -

SELECT DISTINCT id
  FROM TABLE
 WHERE type = 'upload'
   AND id NOT IN (SELECT ID
                    FROM TABLE
                   WHERE type <> 'upload')