CODEWITHSUNDEEP
sql
Ramesh.kbvr
Ramesh.kbvr

Reputation: 783

sql query simplification with less burden

I need to simplify the following query as much as possible either by using joins or any other way...

select polid
  from polver
  where idmkey = 'idm3'
    and polid in ( select polid
                     from polver
                     where idmkey = 'idm2'
                       and polid in ( select polid
                                        from polver
                                        where idmkey='idm1' ) );

Upvotes: 0

Views: 64

Answers (2)

juergen d
juergen d

Reputation: 204766

select polid from polver
where idmkey in ('idm1', 'idm2', 'idm3') 
group by polid
having count(distinct idmkey) = 3

Upvotes: 3

Andomar
Andomar

Reputation: 238086

You could use a group by:

select  polid
from    polver
group by
        polid
having  max(case when idmkey = 'idm1' then 1 end) = 1
        and max(case when idmkey = 'idm2' then 1 end) = 1
        and max(case when idmkey = 'idm3' then 1 end) = 1

Upvotes: 3

Related Questions