Geek asks and answers

MySQL single table, select multiple row value

From the table below, how would I select all animalIds that have a specific combination of attributes, for example. if I provided attributes 455 and 685, I would expect to return animalIds 55 and 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 reliable way?

  SELECT animalId
    FROM animalAttributes
   WHERE attributeId IN (455,685)
GROUP BY animalId 
  HAVING COUNT(DISTINCT attributeId) = 2;
+3
source share
4 answers
SELECT DISTINCT animalId
FROM animalAttributes
WHERE attributeId IN (455,685)

or

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

If you really want to get accurate results, you can go with the same method:

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 needed 3 matching attributes, you could just add another connection:

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
+1
source 
SELECT DISTINCT `animalId` FROM `animalAttributes` WHERE `attributeId` = 455
INTERSECT
SELECT DISTINCT `animalId` FROM `animalAttributes` WHERE `attributeId` = 685
+1

SELECT `a455`.`animalId`
FROM (SELECT `animalId` FROM `animalAttributes` WHERE `attributeId` = 455) AS `a455`
JOIN (SELECT `animalId` FROM `animalAttributes` WHERE `attributeId` = 685) AS `a685`
ON `a455`.`animalId` = `a685`.animalId`

This approach can be expanded to even handle requests such as “animals with attributes 455 and 685 but not 123”, which will be very difficult with a simple one COUNT(DISTINCT).

-1
source

More articles:


All Articles