开发者

MySQL single table, select value based on multiple rows

开发者 https://www.devze.com 2023-03-10 01:20 出处:网络
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 animalI开发者_Go百科ds 55

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 animalI开发者_Go百科ds 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;


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


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


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

or

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

精彩评论

暂无评论...
验证码 换一张
取 消