Use MySQL to SELECT attributes similar to Amazon's SimpleDB

Sorry I couldn't think of a better way to title this. In Amazon's SimpleDB, an item can have multiple values in the same column, so it's possible to select only those items that have all of the attributes being sought.

In MySQL, let's say the following table ("Photo_Attributes") contains an unlimited number of attributes for photographs that are contained in another table ("Photos"), and that the two tables are joined by Item_Number.

And, let's say I wanted to find a hat whose color was red and size was medium, which in this case would be ITEM_ID "ABC" and not "OPQ".

+-----+----------+--------+-----------+-------+  
| ID  | Item_ID  | Object | Attribute | Value |  
+-----+----------+--------+-----------+-------+  
|  1  |  ABC     | Hat    | Color     | Red   |  
+-----+----------+--------+-----------+-------+  
|  2  |  FGH     | Pants  | Color     | Blue  |  
+-----+----------+--------+-----------+-------+  
|  3  |  FGH     | Pants  | Size      | Large |  
+-----+----------+--------+-----------+-------+  
|  4  |  LMN     | Shirt  | Color     | Red   |  
+-----+----------+--------+-----------+-------+  
|  5  |  ABC     | Hat    | Size      | Med   |  
+-----+----------+--------+-----------+-------+  
|  6  |  LMN     | Shirt  | Size      | Med   |  
+-----+----------+--------+-----------+-------+  
|  7  |  OPQ     | Hat    | Color     | White |  
+-----+----------+--------+-----------+-------+  
|  8  |  OPQ     | Hat    | Size      | Med   |  
+-----+----------+--------+-----------+-------+  

---

The followin开发者_Go百科g query would yield no results because each row contains only one Attribute and one Value.

SELECT FROM Photo_Attributes WHERE OBJECT='hat' AND (Attribute='Color" AND Value='Red") AND (Attribute='Size' AND Value='Med');

---

And, this query would produce more rows than it should (i.e., all red and all medium-sized items).

SELECT FROM Photo_Attributes WHERE OBJECT='hat' AND (Attribute='Color" AND Value='Red") OR (Attribute='Size' AND Value='Med');

---

What's the best way to write this -and- is there a way to do it without using JOIN in the SELECT statement? I'm wondering the latter because the query would be programmatically generated (in nodejs) and the number of Attribute-Value pairs could range from one to several. I figure I could also use nested queries, culling from recordset, but that seems equally inefficient.

---

SELECT pa1.Item_ID
    FROM Photo_Attributes pa1
        INNER JOIN Photo_Attributes pa2
            ON pa1.Item_ID = pa2.Item_ID
                AND pa2.Attribute = 'Size'
                AND pa2.Value = 'Med'
    WHERE pa1.Object = 'Hat'
        AND pa1.Attribute = 'Color'
        AND pa1.value = 'Red'

Assuming no overlap in your name/value pairs (e.g., You'd never have Size/Red or Color/Med), you could probably also do something like this.

SELECT pa.Item_ID
    FROM Photo_Attributes pa
    WHERE pa.Object = 'Hat'
        AND pa.Attribute IN ('Size', 'Color')
        AND pa.Value IN ('Med', 'Red')
    GROUP BY pa.Item_ID
    HAVING COUNT(DISTINCT Value) = 2