SQL n-to-n matching multiple values

Question

I've got a web application that matches images to tags, and I need to create a way of dynamically refine results for tag search. However, I cannot find a clean way to make that SQL queries, and that's where I need your help.

The idea is that if I search for tags "clean" and "dog", I will have image results that have both the tags "clean" and "dog". If I also include the tag "little", my results would have to narrow down to images that have the three tags associated.

So, having an N-to-N relation, which is the correct way to do this?

My natural approach was generating code something like this, but I certainly don't like where it is going:

SELECT images.*
FROM images
INNER JOIN image_tags ON ...
INNER JOIN tags ON ...
WHERE tags.tag = @tag1
AND EXISTS
(
  SELECT 1
  FROM images 
  INNER JOIN image_tags ON ...
  INNER JOIN tags ON ...
  WHERE tag = @tag2
  AND EXISTS
  (
    SELECT 1
    FROM images 
    INNER JOIN image_tags ON ...
    INNER JOIN tags ON ...
    WHERE tag = @tag3
    AND EXISTS (...)
    ...
  )
)

Certainly, that's not really good. Any idea?

Thanks!

Answer 1

Something like that could work (I use id for SELECT and GROUP BY, use the columns you need.

SELECT images.id
FROM images
INNER JOIN image_tags ON ...
INNER JOIN tags ON ...
WHERE tags.tag IN ( @tag1, @tag2, @tag3 )
GROUP BY images.id
HAVING COUNT(*) = @number_of_tags

If you have 3 tags like in your example then number_of_tags would have to be 3, and the join would result in 3 rows per id that matches.

You can either create that query dynamically, or define it with, say, 10 tags and initialize them with a value that won't occur in tags.