SQL n-to-n matches multiple values

Question

I have a web application that matches images with tags, and I need to create a way to dynamically refine my tag search results. However, I cannot find a clean way to make these SQL queries and that is where I need your help.

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

So, with an N-to-N relationship, what is the right way to do this?

My natural approach created code similar to this, but of course I don't like where it goes:

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

Of course, this is not very good. Any idea?

Thanks!

Answer 1

Maybe something similar (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, as in your example, then number_of_tags should be equal to 3, and the connection will result in 3 lines matching id .

You can either create this query dynamically, or define it using, say, 10 tags and initialize them with a value that will not occur in the tags.

Answer 2

I would not use the NN relation, but a text field for storing tags.

This may sound dirty as we lose normality, but tags are usually used for text search, and disk space is cheap.

Then you can run

 SELECT * FROM images WHERE tags LIKE '%clean%' AND tags LIKE '%dog%'... 

Answer 3

Using intersection, you can do this:

 SELECT images.* FROM images WHERE image_id IN ( SELECT image_id FROM image_tags WHERE tag_id = (SELECT tag_id FROM tags WHERE tag = @tag1) INTERSECT SELECT image_id FROM image_tags WHERE tag_id = (SELECT tag_id FROM tags WHERE tag = @tag2) INTERSECT .... ) 

This will display all images based on intersection tags (matching all) in image_tags.