Better way to avoid joining the same table multiple times

Question

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

I would need to retrieve the animalid if both teeth and lege are 'Yes'
I have written a query like

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)

and its working.

would like to know is there any better way to write this and achieve the same result.

Answer 1

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'

Better way to avoid joining the same table multiple times

Sign up