In an HABTM relation, how can find items that must meet more then one criteria on the related table

Hope someone understood the title...

Let's say I have the following relation, Animals HABTM Tags.

animals table

id  name
1   dog
2   whale
3   Elephant

tags table:

id  name
1   brown
2   gray
3   terrestrial
4   aquatic

the link table

animal_id   tag_id
1           1
1           3
2           2
2         开发者_运维知识库  4
3           2
3           3

I'd like to retrieve all animals that have Tag = gray AND Tag = aquatic

It is easy with only one criteria

SELECT * FROM `animals` as a 
left join animals_tags at on a.id = at.animal_id
Where at.tag_id = (select id from tags where name = 'brown')

Is it possible to make a single query for more then one tag? Or should I retrieve results for each tags and then extract the intersecting results?

---

Note: I user tag ids instead of tag names.

If you want to select 1 row per animal:

SELECT a.*, GROUP_CONCAT(t.name) tags
FROM `animals` as a 
  LEFT JOIN animals_tags at ona.id = at.animal_id
  LEFT JOIN tags t ON at.tag_id = t.id
GROUP BY a.id
HAVING SUM(at.tag_id = 1) > 0 AND SUM(at.tag_id = 4) > 0

If you also want to retrieve all the tags for each animal:

SELECT a.*, t.*
FROM animals a JOIN ( 
    SELECT animal_id
    FROM animals_tags
    GROUP BY animal_id
    HAVING SUM(tag_id = 1) > 0 AND SUM(tag_id = 4) > 0
  ) ag ON a.id = ag.animal_id
  LEFT JOIN animals_tags at ON a.id = at.animal_id
  LEFT JOIN tags t ON at.tag_id = t.id

---

INTERSECT would be nice if MySQL had it, but you could accomplish this with a HAVING clause, in theory.

SELECT a.name FROM `animals` as a 
left join animals_tags at ona.id = at.animal_id
Where at.tag_id = (select id from tags where name = 'brown')
OR at.tag_id = (select id from tags where name = 'terrestrial')
GROUP BY a.name
HAVING COUNT(a.name) = 2