Can you solve this simple SQL query?

Question

Suppose this is a site that sells cameras. Here are my entities (tables):

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

The fact is, between the cameras and the function, I have a Many to Many relationship, so I have an additional table:

 camera -> cameras_features -> feature 

So the request is simple:

How to get all cameras with function 1,2 and 3?

This is similar to building a raster image index.

Data you can use to check if this solution is normal

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

Here are the queries and the expected result:

• Show all cameras with functions 1,2 and 3: C1
• Show all cameras with functions 1,2 and 4: C2
• Show all cameras with functions 1 and 2: C1 , C2 and C3

Here is what I did (it works, but it is really ugly, does not 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 DISTINCT Camera.* FROM Camera c INNER JOIN cameras_features fc1 ON c.id = fc1.camera_id AND fc1.feature_id = 1 INNER JOIN cameras_features fc2 ON c.id = fc2.camera_id AND fc2.feature_id = 2 

What happens here is that the cameras will be filtered to cameras with function 1, and then in this group the cameras will be filtered to those who have function 2

Answer 2

 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 - the number of functions in (1,2,3) . And assuming that (camera_id,feature_id) is unique in camera_features .

Answer 3

This is easiest to generalize by putting search values ​​in a table ...

 INSERT INTO search SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 SELECT camera_features.camera_id FROM camera_features INNER JOIN search ON search.id = camera_features.feature_id GROUP BY camera_features.camera_id HAVING COUNT(DISTINCT camera_features.feature_id) = (SELECT COUNT(DISTINCT id) FROM search)