Can you solve this simple SQL query?

Question

Suppose it's a website that sells photo cameras. Here are my entities (tables):

Camera: A simple camera
Feature: A feature like: 6mp, max resolution 1024x768, 

The thing is between cameras and feature i've got a Many to Many relationship, so i have an extra table:

camera -> cameras_features -> feature

So, the query is simple:

How to get all the cameras that have the feature 1,2 and 3?

It's like constructing a bitmap index.

Data you can use to test if the solution is ok

C1 has features 1,2,3
C2 has features 1,2,4
C3 has features 1,2

Here are querys and the expected result:

• Show all the cameras which have feature 1,2 and 3: C1
• Show all the cameras which have feature 1,2 and 4: C2
• Show all the cameras which have feature 1 and 2: C1, C2 and C3

Here is what i did (it works, but it's really ugly, don't want to use it):

SELECT * FROM camera c

WHERE c.id IN (    
    (SELECT c.id FROM camera c JOIN cameras_features f ON (c.id=f.camera_id)
    WHERE f.feature_id=1)
        q1 JOIN -- simple intersect
    (SELECT c.id FROM camera c JOIN cameras_features f ON (c.id=f.camera_id)
    WHERE f.feature_id=2)
        q2 JOIN ON (q1.id=q2.id)
)

Answer 1

SELECT camera.id
FROM camera JOIN camera_features ON camera.id=camera_features.camera_id
GROUP BY camera.id
HAVING sum(camera_features.feature_id IN (1,2,3))=3

3 is the number of features in (1,2,3). And assuming (camera_id,feature_id) is unique in camera_features.