hinklea hinklea - 17 days ago 8
MySQL Question

Two Columns, Same Data, Different Where Clauses

I'm needing to query the same column with two different WHERE clause statements.

I'm working with Magento and I'm unable to change the table format.

Table View:

catalog_product_entity_varchar


entity_id | attribute_id | value |
5 | 60 | Bear Apprentice |
5 | 86 | bear-apprentice |
5 | 71 | Item Description |
5 | 74 | /a/p/apprentice2.jpg |


I would like to have it displayed as:

entity_id | Item Name | img |
5 | Bear Apprentice | /a/p/apprentice2.jpg |


Ultimately getting
attribute_id 60
and
74
to appear on the same row but in two separate columns.
Is it possible to do a WHERE clause on a column alias?

Thanks!

Answer

In mySQL one way to pivot is to use case and max and group by. This does assume an entity_Id can only have one paired value (1 attribute per value per entity) e.g. 60 can only appear once for entity 5.

SELECT entity_ID
     , max(case when attribute_Id = 60 then value end) as `Item Name`
     , max(case when attribute_Id = 74 then value end) as img
FROM tableName
WHERE entity_ID = 5
GROUP BY entity_ID