I have a table Animal with the structure given below
AnimalId Feature Present
-------- ------- -------
1 Teeth Yes
1 Leg Yes
2 Teeth No
2 Leg Yes
3 Teeth Yes
3 Leg Yes
select distinct A1.AnimalId from Animal A1
inner join Animal A2 on
A1.AnimalId =
(select distinct A2.AnimalId from Animal A2
inner join Animal A3 on
A2.AnimalId =
(select distinct A3.AnimalId from Animal A3 where A3.Feature = 'Leg' and A3.Present = 'Yes' group by A3.AnimalId)
where A2.Feature = 'Teeth' and A2.Present = 'Yes' group by A2.AnimalId)
I like to approach this type of query using group by
and having
. In your case:
select a.animalId
from animal a
where (a.feature = 'Teeth' and a.present = 'Yes') or
(a.feature = 'Leg' and a.present = 'Yes')
group by a.animalId
having count(distinct a.feature) = 2;
The where
clause can be simplified to:
where a.feature in ('Teeth', 'Leg') and a.present = 'Yes'