CODEWITHSUNDEEP
mysqlsql
raider5
raider5

Reputation: 91

MySQL single table, select value based on multiple rows

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;

Upvotes: 3

Views: 1744

Answers (3)

bpgergo
bpgergo

Reputation: 16057

SELECT DISTINCT animalId
FROM animalAttributes
WHERE attributeId IN (455,685)

or 

SELECT animalId
FROM animalAttributes
WHERE attributeId IN (455,685)
GROUP BY animalId

Upvotes: 1

Halcyon
Halcyon

Reputation: 57721

SELECT DISTINCT `animalId` FROM `animalAttributes` WHERE `attributeId` = 455
INTERSECT
SELECT DISTINCT `animalId` FROM `animalAttributes` WHERE `attributeId` = 685

Upvotes: 1

Fosco
Fosco

Reputation: 38526

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

Upvotes: 1

Related Questions