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

But maybe this can help you:

SELECT * 
FROM tbl t1
INNER JOIN tbl t2 ON t1.Name = t2.Name
WHERE t1.Attribute = 'green' AND t2.Attribute = '4 legs'

Answer 2

create two tables, one of items and one of attributes.
Items could be name, intAttributeID, where intAttributeID is a foreign key reference to the Attributes table. That way you can do a select statement based off whatever you care about.

Answer 3

You could also query each attribute separately, and then intersect them...

/*
-- create sample table...
create table #temp1
    (item varchar(max),
    attrib varchar(max))

-- populate sample table (SQL 08)...
insert #temp1
values ('frog', 'green'), ('cat', 'furry'), ('frog', 'nice'), ('cat', '4 legs'), ('frog', '4 legs')
*/


SELECT  item
FROM    #temp1
WHERE   attrib = 'green'
INTERSECT
SELECT  item
FROM    #temp1
WHERE   attrib = '4 legs'

Answer 4

select
    item, count(*)
from
    @temp
where
    attribute in ('4 legs','green')
group by
    item
having
    count(*) = 2 -- this "2" needs to be replaced with however many attributes you have

Answer 5

The most efficient way to do this is with a self-join:

SELECT * FROM attributes a1 
JOIN attributes a2 USING (item_name) -- e.g. frog
WHERE a1.value = 'green' AND a2.value = '4 legs';

Another solution that some people use is a trick with GROUP BY:

SELECT item_name FROM attributes
WHERE value IN ('4 legs', 'green')
GROUP BY item_name
HAVING COUNT(*) = 2;

But the GROUP BY solution may not be as efficient as a JOIN, depending on which brand of RDBMS you use. Also one method may scale better as the volume in your table grows.

Answer 6

If possible, I would redesign. This is not something you will ever be able to effectively query 12 values on at the same time on (it will require 12 joins)

Please read this wikipedia article http://en.wikipedia.org/wiki/Entity-Attribute-Value_model#Downsides

Never seen a database yet that used this model that didn't run into serious performance issues eventually. This design looks elegant to non-database people but is actually usually a sign of a badly designed database.