MySQL single table, select value based on multiple rows

Question

From the table below, how would I select all animalIds that have a specific combination of attributeIds e.g. if I supplied attributeIds 455 & 685 I'd expect to get back animalIds 55 & 93

Table name: animalAttributes

id      attributeId     animalId
1       455             55
2       233             55
3       685             55
4       999             89
5       455             89
6       333             93
7       685             93
8       455             93

I have the following query that seems to work, however, I'm not sure if there is a more robust way?

  SELECT animalId
    FROM animalAttributes
   WHERE attributeId IN (455,685)
GROUP BY animalId 
  HAVING COUNT(DISTINCT attributeId) = 2;

Answer 1

If you really want accurate results, you could go with a fool-proof method like this:

select distinct base.animalId
from animalAttributes base
join animalAttributes a on base.animalId = a.animalId
     and a.attributeId = 455
where base.attributeId = 685

If you later needed 3 matching attributes, you could just add another join:

select distinct base.animalId
from animalAttributes base
join animalAttributes a on base.animalId = a.animalId
     and a.attributeId = 455
join animalAttributes b on base.animalId = b.animalId
     and b.attributeId = 999
where base.attributeId = 685