priya madhavan priya madhavan - 1 year ago 108
SQL Question

Better way to avoid joining the same table multiple times

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 Source

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'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download