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?

Sententious

What do you mean by item/attribute list? XML Attributes? – Precarious

so many people putting forth such good answers on such a ambiguous illogical question, good luck getting your 10 points! – Macomber

Its just a normal sql table, if it helps the table is called Pets and it has two fields items and attributes. I don't see how the question is illogical. – Sententious

@Jason, are you saying you have a column that has both "green" and "4 legs" in it? – Rancho

@Jason Christa said "I want to select the item the has 4 legs and is green (the frog), what is the most efficient SQL statement to do this?" -- you don't find that a ambiguous illogical question? ;-) – Macomber

@KM: I thought the original question was fine. Could it have been more clear? Of course. But it was easy to understand what Jason wanted, which is a lot better than some questions on StackOverflow. – Ceria

Learn about relational division & expressing it in SQL. – Gitagitel

Answer 1

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

Grouping

+1 for remembering to use DISTINCT. The OP didn't give the actual table definition, so we don't know if a given item/attribute pair must be unique. – Ceria

Answer 2

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.

Ceria

Why would you say your first solution is more efficient? – Sententious

@Jason: My experience with MySQL is that the GROUP BY solution requires writing an interim result to a temporary table, which usually kills performance. The JOIN solution employs indexes much better. It may even use covering indexes, and never touch the table. – Ceria

Good to know as I am using MySQL. I wonder what would happen if I was querying on 12 values. – Sententious

You should make sure to analyze the optimization plan with the EXPLAIN statement, and learn how to read the output. Refer to docs dev.mysql.com/doc/refman/5.1/en/explain.html – Ceria

Answer 3

select * from table where thing='frog'

nothing beats knowing exatcly what you want.

Herr

At 1st I thought about that but, I'm not sure the OP is asking "just that". ;) +1 anyway. – Precarious

ha ha ha. 100% correct and 100% wrong at the same time I suspect – Leftist

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

Infusive

Answer 5

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'

Nippur

Answer 6

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

Leftist

Why do you say the table not normalized? – Ceria

First impressions... I should have said "harder to use for some kinds of queries" – Leftist

KM hit the nail on the head too in comment on OP – Leftist

Answer 7

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.

Peavey

Think of this table as the intermediate table between a many-to-many relationship between items and attributes. – Sententious

Answer 8

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'

Worthen

Answer 9

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.

Who