Geek Answers Handbook

What is an SQL statement for selecting an item with multiple attributes in a list of elements / attributes?

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

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

In the items column, I want to select unique objects that have the green attribute and 4 legs. In this case, I would expect to return only the frog object. What is the most efficient query for this?

+5
source share
9 answers
select  item.name 
from    item 
where   item.attribute in ('4 legs', 'green') 
group by item.name 
having  count(distinct item.attribute) = 2
+9
source

The most efficient way to do this is to connect yourself:

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 the GROUP BY trick:

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

GROUP BY , JOIN, , RDBMS . .

+2

* , = ''

, .

+1
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
+1

, ...

/*
-- 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'
+1

, . .

, , .

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
0

, .
, intAttributeID, intAttributeID - . , select, , .

0

:

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

, . , - 12 ( 12 )

, http://en.wikipedia.org/wiki/Entity-Attribute-Value_model#Downsides

I have never seen a database that used this model, which ultimately did not cause serious performance problems. This design looks elegant for people without a database, but it is usually a sign of a poorly designed database.

0
source

More articles:


All Articles