Re: Query problem

[laptop alias]

This is a daft setup. People have gone insane trying to use EAV models before. Mix that with alcohol and you've got a recipe for disaster. That said, I'll show you the principle and leave you to it.

Consider this example:

SELECT * FROM  eav_hell;
+--------------------+-----------+-------+
| entity             | attribute | value |
+--------------------+-----------+-------+
| Merlot             | Colour    | Red   |
| Merlot             | Year      | 2009  |
| Merlot             | Size      | 75cl  |
| Cabernet Sauvignon | Colour    | Red   |
| Cabernet Sauvignon | Year      | 2008  |
| Cabernet Sauvignon | Size      | 75cl  |
| Pinot Grigio       | Colour    | White |
| Pinot Grigio       | Year      | 2008  |
| Pinot Grigio       | Size      | 75cl  |
+--------------------+-----------+-------+

SELECT entity Wine
     , MAX(CASE WHEN attribute = 'Colour' THEN value END) Colour
     , MAX(CASE WHEN attribute = 'Year' THEN value END) Year
     , MAX(CASE WHEN attribute = 'Size' THEN value END) Size
  FROM eav_hell
 GROUP
    BY Wine;
+--------------------+--------+------+------+
| Wine               | Colour | Year | Size |
+--------------------+--------+------+------+
| Cabernet Sauvignon | Red    | 2008 | 75cl |
| Merlot             | Red    | 2009 | 75cl |
| Pinot Grigio       | White  | 2008 | 75cl |
+--------------------+--------+------+------+

Pity the poor, abandoned data types.