SQL: Select * where attribute matches all items in array

Working in a Rails App, I have the following table structure (pertinent columns only)

Photos (id: integer) Taggings (photo_id: integer, tag_id: integer) Tags (id: integer, name:string)

I have the following SQL query:

SELECT distinct photos.* 
FROM \"photos\" INNER JOIN \"taggings\" ON \"photos\".\"id\" = \"taggings\".\"photo_id\" 
                INNER JOIN \"tags\"     ON \"tags\".\"id\" = \"taggings\".\"tag_id\" 
开发者_运维百科WHERE \"tags\".\"name\" IN ('foo', 'bar')

When I generate this query I'm passing in an array of tags (in this case ["foo","bar"]). The query correctly searches for photos that match ANY of the tags passed in the array.

How can I change this query to select records with ALL of the given tags (ie a photo only matches if tagged with "foo" AND "bar", instead of selecting records with ANY of the given tags?

---

There may be a better way, but this should do it

SELECT photos.id,max(otherColumn)
FROM \"photos\" 
INNER JOIN \"taggings\"
ON \"photos\".\"id\" = \"taggings\".\"photo_id\" 
INNER JOIN \"tags\" 
ON \"tags\".\"id\" = \"taggings\".\"tag_id\" 
WHERE \"tags\".\"name\" IN ('foo', 'bar')
group by photos.id
having count(*) = 2 --2 is the number of items in your array of tags.

---

If you are in rails you don't need query to do this.

Tags.find(1).taggings should give you an array of all photos with that tag

you can also use Tags.find_by_name("foo").taggings

you can similarly iterate over all tags, and collect the arrays and then just do something like on the arrays you have got.

[ 1, 1, 3, 5 ] & [ 1, 2, 3 ]   #=> [ 1, 3 ]

Basically 'and' the arrays and get the unique photos.This way you can get the photos that match all the tags.