And multiple values like IN clause

Question

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

For several other ways to achieve the same thing: [How to filter SQL results in a has-many-through relation](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation) — ypercubeᵀᴹ, Jul 30 '12 at 15:58

juergen d · Accepted Answer · 2012-07-30T14:55:54.430

2

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

edited Jul 30 '12 at 14:55

answered Jul 30 '12 at 14:11

juergen d

201,996
37
293
362

1

If a car has feature 'f1' listed twice in the table (perhaps unlikely, but it could still be an issue), it would appear as a result in your query. That is why is always use the DISTINCT count for these kinds of queries. – Joe Stefanelli Jul 30 '12 at 14:28
You are right. I added it to make it work in every case. Thanks. – juergen d Jul 30 '12 at 14:56

score 2 · Answer 2 · answered Jul 30 '12 at 14:12

2

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;

answered Jul 30 '12 at 14:12

Joe Stefanelli

132,803
19
237
235

Joe, can I get both the car and features in the result? – Kiranshell Jul 30 '12 at 14:47
@Kiranshell: Don't you already know the features since you're testing for them in the IN clause? – Joe Stefanelli Jul 30 '12 at 15:37
Joe, The Car and Feature was a simplified e.g of my requirement, the actual query has hundreds of values in the IN clause. I have to send this result to users after exporting it to a spreadsheet, so I need the features also in the result. – Kiranshell Jul 30 '12 at 15:42

Holger Brandt · Answer 3 · 2012-07-30T14:15:17.157

0

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

edited Jul 30 '12 at 14:15

answered Jul 30 '12 at 14:10

Holger Brandt

4,324
1
20
35

And multiple values like IN clause

3 Answers3