CODEWITHSUNDEEP
sqlin-clause
Kiranshell
Kiranshell

Reputation: 267

And multiple values like IN clause

How do I get the Cars and Features which have both Feature1 & Feature2 in below table (BMW and Toyota)?

Select Car,Feature 
    from Table 
    where Feature in ('Feature1','Feature2')

gives all 3 cars, including Honda which has Feature1 only. This is just a simplified example of the actual query, which can have hundreds of values in the IN clause. 

Car    Feature
-----  --------
BMW    Feature1
BMW    Feature2
BMW    Feature3
Toyota Feature1
Toyota Feature2
Honda  Feature1

Thanks, Kiran

Upvotes: 1

Views: 864

Answers (3)

juergen d
juergen d

Reputation: 204766

select Car
from your_table
where feature in ('f1', 'f2')
group by car
having count(distinct feature) >= 2

Upvotes: 2

Holger Brandt
Holger Brandt

Reputation: 4354

The IN clause is effectively a nested OR. The following will get you all the cars that have both features.

SELECT U.Car FROM
  (
   Select Car from Table where Feature ='Feature1'
   UNION ALL
   Select Car from Table where Feature ='Feature2'
  ) AS U
WHERE COUNT(U.Car) > 1

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

Use a GROUP/HAVING construct where the HAVING tests for the number of elements in your IN clause. This way you guarantee that both features are present.

SELECT cf.Car
    FROM CarFeature cf
    WHERE cf.Feature IN ('Feature1', 'Feature2')
    GROUP BY cf.Car
    HAVING COUNT(DISTINCT cf.Feature) = 2;

Upvotes: 2

Related Questions