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;

I think this query will not give you what you want. It is conceivable for an animal to have 455 attribute, and another, say, 123. This would return since it has two. — tofutim
– tofutim, Commented May 31, 2011 at 23:40
There's no need for Having clause HAVING COUNT(DISTINCT attributeId) = 2 — bpgergo
– bpgergo, Commented May 31, 2011 at 23:42
The having clause is ensuring they have both attributes. This query looks OK to me. — MarkD
– MarkD, Commented May 31, 2011 at 23:45
I think if (attributeId, animalId) is unique, you don't need the DISTINCT. — eaolson
– eaolson, Commented May 31, 2011 at 23:50

Fosco · Accepted Answer · 2011-05-31 23:46:33Z

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

answered May 31, 2011 at 23:46

Fosco

38.6k7 gold badges90 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

raider5 Over a year ago

when you say "fool-proof", how is this more robust? Thanks

Fosco Over a year ago

@raider5 it was directed at the OPs query that he says 'seems to work.' Also some of the other answers don't give me the warm fuzzies that they will work.

Halcyon · Accepted Answer · 2011-05-31 23:47:16Z

1

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

answered May 31, 2011 at 23:47

Halcyon

57.7k10 gold badges93 silver badges131 bronze badges

Comments

bluish · Accepted Answer · 2012-07-16 08:19:04Z

1

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

or

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

edited Jul 16, 2012 at 8:19

bluish

27.5k28 gold badges126 silver badges185 bronze badges

answered May 31, 2011 at 23:37

bpgergo

16.1k5 gold badges48 silver badges70 bronze badges

2 Comments

MarkD Over a year ago

This will return animalIds even if they only have one of the attributeId values.

raider5 Over a year ago

@MarkD - It needs to be based on a combination of 455 AND 685 rather than 455 OR 685. I'm guessing your query would return animalIds 55,89,93?

Collectives™ on Stack Overflow

MySQL single table, select value based on multiple rows

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related