What is a SQL statement to select an item that has several attributes in an item/attribute list?

Question

Say I have a table that has items and attributes listed like,

frog    green
cat     furry
frog    nice
cat     4 legs
frog    4 legs

From the items column I want to select unique objects that have both the green and 4 legs attribute. I would expect to get back just the frog object in this case. What is the most efficient query to do this?

Answer 1

select  item.name 
from    item 
where   item.attribute in ('4 legs', 'green') 
group by item.name 
having  count(distinct item.attribute) = 2