Single Column Multiple Filter In Mysql Intersection
Here is a table
CarID| Attribute | Value
1 | Color | Red
2 | Color | Blue
3 | Color | Red
1 | Type | Coupe
2 | Type | Hatch Back
3 | Type | Coupe
3 | Make | Honda
2 | Make | Toyota
1 | Make | Ford
Now I would like to run a filter Like Select * From 开发者_StackOverflow中文版Cars WHERE (Attribute = Color AND Value = Red) AND (Attribute = Make AND Value = Honda).... and Hope to get the CarID as 3 !
This is simple case of Intersection of 2 queries but I don't know how to get it done in a single query.
Any help appriciated.
select
carid,
count(*) matchedItems
from
YourTable
where
( Attribute = 'Color' and Value = 'Red' )
OR ( Attribute = 'Make' and Value = 'Honda' )
group by
carid
having
matchedItems = 2;
you may have to change the having to...
having count(*) = 2
The most straightforward solution would be to simulate a separate table for each attribute with sub queries (although it might not be the most efficient):
SELECT Colors.CarID FROM
( SELECT CarID, Value FROM Cars WHERE Attribute = 'Color' ) Colors,
( SELECT CarID, Value FROM Cars WHERE Attribute = 'Make' ) Makes
WHERE Colors.CarID = Makes.CarID AND Colors.Value = 'Red' AND
Makes.Value = 'Honda';
Here is another variant of the query
SELECT carid
FROM AnotherUnknownTableName t INNER JOIN
AnotherUnknownTableName t2 ON t.carid = t2.carid AND
t.Attribute = 'Color' AND
t2.Attribute = 'Make'
WHERE
t.Value = 'Red' AND t2.Value = 'Honda'
Having an index on (carid, Attribute) will do wonders.
精彩评论