CODEWITHSUNDEEP
sqloracle-database
Kiranshell
Kiranshell

Reputation: 267

In Oracle how to search with sub conditions on same column

In Oracle how do I find Cars which must have Feature1 and have at lest, one out of Feature2 or Feature3. Sample table and expected result should look like below screenshot. Thanks Kiran enter image description here

Upvotes: 0

Views: 446

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

I like to do this with GROUP BY and HAVING:

select car
from t
group by car
having max(case when feature = 'Feature1' then 1 else 0 end) = 1 and
       max(case when feature in ('Feature1', 'Feature2') then 1 else 0 end) = 1

This query returns the cars. To get the featuers as well, you have to join tis back in:

select t.*
from (select car
      from t
      group by car
      having max(case when feature = 'Feature1' then 1 else 0 end) = 1 and
             max(case when feature in ('Feature1', 'Feature2') then 1 else 0 end) = 1
     ) c join
     t
     on t.car = c.car

I like this method, because the same idea can be used for handling many different similar queries -- AND conditions, OR conditions, different subgroups, and different counts.

Upvotes: 0

Taryn
Taryn

Reputation: 247630

This should work:

select t1.car, t1.feature
from yourtable t1
inner join
(  -- inner select returns the cars with the Feature1 and Feature2 or Feature3
  select car, feature
  from yourtable 
  where feature = 'Feature1'
    and exists (select car
                from yourtable 
                where feature in ('Feature2', 'Feature3'))
) t2
  on t1.car = t2.car
where t1.feature in ('Feature1', 'Feature2', 'Feature3') -- this excludes any other features

see SQL Fiddle with Demo

Upvotes: 1

Related Questions