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)
)

I'm ashamed, this is not homework, but the tag is ok. – santiagobasulto Feb 01 '12 at 20:38 — santiagobasulto, Feb 01 '12 at 20:38

Michael Krelin - hacker · Answer 1 · 2012-02-01T20:43:56.813

5

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.

edited Feb 01 '12 at 20:43

answered Feb 01 '12 at 20:38

Michael Krelin - hacker

138,757
24
193
173

That assumption is ok. In fact I've a UNIQUE on those columns. – santiagobasulto Feb 01 '12 at 20:43
No, I was thinking ahead already, thanks, fixed. (that was about group by). – Michael Krelin - hacker Feb 01 '12 at 20:43
And I'm assuming you can get the rest from your tables having the list of ids, either cramming it into this query or using it as a subquery. Your attempt suggests you're capable of that :) – Michael Krelin - hacker Feb 01 '12 at 20:45
It works! Really nice stuff! About changing my tables, i simplified the example ;) It's a little more complicated. Where did you get this stuff? I'm amazed. – santiagobasulto Feb 01 '12 at 20:51

Bassam Mehanni · Accepted Answer · 2012-02-02T00:34:12.217

3

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 is happening here is that cameras will be filtered down to cameras with feature 1, then within this group, the cameras are gonna be filtered down to the ones with feature 2

edited Feb 02 '12 at 00:34

answered Feb 01 '12 at 20:38

Bassam Mehanni

14,796
2
33
41

1

This works! I can't tell you the emotion i feel right now. It seems similiar to what i did, but with the JOINS "trick", what is it called? – santiagobasulto Feb 01 '12 at 20:47
And you probably don't need the `DISTINCT`. – ypercubeᵀᴹ Feb 01 '12 at 20:56
2

See also this question: [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) with tens of other ways to achive the same results, with **benchmarks**, too (they are for Postgres, but you'll see that the multiple JOIN method is quite fast). – ypercubeᵀᴹ Feb 01 '12 at 20:58
not sure if the inner join filtering has a specific name, but glad I can help. @ypercube not sure if I need it or not in MySQL, but in MS SQL there would be at least 2 records per camera. – Bassam Mehanni Feb 01 '12 at 21:00
1

If `(camera_id, feature_id)` is `UNIQUE` in table `cameras_features` (and it should be Unique), then you don't need the `distinct`. – ypercubeᵀᴹ Feb 01 '12 at 21:05
Good question, couldn't find anything similar before. Seems like the HAVING answer has "better" EXPLAIN, but i should add data and benchmark it – santiagobasulto Feb 01 '12 at 21:12
@ypercube aren't we getting a record for feature 1, and a record for feature 2? – Bassam Mehanni Feb 02 '12 at 00:31

score 1 · Answer 3 · answered Feb 01 '12 at 20:39

This is easiest to generalise by putting the search values into 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)

Can you solve this simple SQL query?

3 Answers3

Linked