I'm a database newb, so please forgive me if this has been covered a million times before, I'm not finding a solution for what I'm trying to do.
I have a table - we'll call 'product_attributes', where a number of specific attributes are stored for all the products. In this table, 'attribute_id' tells me what type of information is stored in the row, 'store_id' tells me which website the information is displayed on, 'entity_id' tells me what product the information is about and 'value' is information about the product. Format is:
value_id entity_type_id attribute_id store_id entity_id value
1221 4 57 0 306 Detailed Description of Product
1222 4 58 0 306 Quick Overview of Product
1223 4 68 0 306 metakeywords
1224 4 89 0 306 metadescription
1225 4 93 0 306 Other Stuff
1226 4 57 0 307 Detailed Des开发者_开发技巧cription of Product
1227 4 58 0 307 Quick Overview of Product
1228 4 68 0 307 metakeywords
1229 4 89 0 307 metadescription
1230 4 93 0 307 Other Stuff
I need to run a query to pull all items from the column 'value' with 'attribute_id=57' into a column called 'Long Description' and all items from the same column with 'attribute_id=58' into another column called 'Short Description'. I can get the values individually easy enough with:
SELECT product_attributes.value FROM product_attributes WHERE attribute_id=57
or
SELECT product_attributes.value FROM product_attributes WHERE attribute_id=58
But I need a separate column for each like this:
Long Description Short Description
Detailed Info of 'entity_id 306' Overview of 'entity_id 306'
Detailed Info of 'entity_id 307' Overview of 'entity_id 307'
SELECT pr1.value as LongDescription, pr2.value as ShortDescription
FROM product_attributes pr1
JOIN product_attributes as pr2
WHERE pr1.attribute_id=57 AND pr2.attribute_id=58
select a.value as 'long desc', b.value as 'short desc'
from
(SELECT entity_id, product_attributes.value FROM product_attributes WHERE attribute_id=57 ) a,
(SELECT entity_id, product_attributes.value FROM product_attributes WHERE attribute_id=58 ) b
where a.entity_id = b.entity_id
select (SELECT a.value FROM product_attributes as a where a.attribute_id=57) as LongDescription,(SELECT b.value FROM product_attributes as b where b.attribute_id=58) as ShortDescription from product_attributes
This may help U..
精彩评论