oracle - SQL get group value only if parameters exist in entire group values -

i have problem solve in oracle sql. given table below, query pass list names , if represent single animal group, return animal string.

 +------+------+ |animal|name  | +------+------+ |dog   |lacy  | |dog   |champ | |dog   |buddy | |cat   |muffin| |cat   |champ | |fish  |wanda | +------+------+ 

for example when pass it: name in ('champ', 'muffin') returns:

 +------+ |animal| +------+ |cat   | +------+ 

but if pass it: name in ('champ', 'wanda') returns:

  +------+ |animal| +------+ |fish  | +------+ 

because parameters did not contain of cat names, did within contain of fish names.

last example: name in ('champ', 'wanda', 'lacy', 'muffin') returns

  +------+ |animal| +------+ |cat   | |fish  | +------+ 

i think want:

 select animal  t  name in ('champ', 'wanda', 'lacy', 'muffin')  group animal  having count(*) = (select count(*) t t2 t2.animal = t.animal) 

it filters table names looking for. aggregates query. having clause compares count of things name total count in table.

you can write as:

 select animal  t  group animal  having count(*) = sum(case when name in ('champ', 'wanda', 'lacy', 'muffin') 1 else 0 end) 

this better because eliminates subquery.