What is a SQL statement to select an item that has

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 * from table where thing='frog'

nothing beats knowing exatcly what you want.

Answer 2

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

Answer 3

Hard because it's not a normalised model. It's a weekend.

You are filtering across multiple, unconnected rows, so you'd have to extract each attribute in turn and then match items.

SELECT
   item
FROM
    (SELECT
        item
    FROM
        Mytable
    WHERE
        attribute = '4 legs') k1
    JOIN
    (SELECT
        item
    FROM
        Mytable
    WHERE
        attribute = 'green') k2 ON k1.item = k2.item