![](/style/images/good.png)
![](/style/images/bad.png)
How to make this query eav to do a horizontal result
source link: https://www.codesd.com/item/how-to-make-this-query-eav-to-do-a-horizontal-result.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
How to make this query eav to do a horizontal result
The case:
tables:
product:
product_id|name |
------------------------
1 |iphone 4 |
2 |gallaxy 2 |
3 |blackbery 6 |
product_attribute:
id|product_id|attribute_id
--------------------------------------------------
1 |1 |2
2 |1 |6
. . .
attribute:
------------------------------
attribute_id|name |value|
1 |width |300
2 |width |320
3 |width |310
4 |height|390
5 |height|370
6 |height|380
should get result:
product_id|height|width
1 |380 |320
......................
Edit: height and width attributes its only part of product attributes - product need to have dynamic ability to be added by the user in backend as it done in magento, because that i choose eav db design. Please write queries if it possible in case we dont know which names product have.
Thanks
There are several ways to implement this. Something like this should work joining back on the table multiple times for each attribute value:
SELECT p.product_id,
a.value height,
a2.value width
FROM Product p
JOIN Product_Attribute pa ON p.product_id = pa.product_id
JOIN Attribute a ON pa.attribute_id = a.attribute_id AND a.name = 'height'
JOIN Product_Attribute pa2 ON p.product_id = pa2.product_id
JOIN Attribute a2 ON pa2.attribute_id = a2.attribute_id AND a2.name = 'width'
And here is the Fiddle.
Here is an alternative approach using MAX and GROUP BY that I personally prefer:
SELECT p.product_id,
MAX(Case WHEN a.name = 'height' THEN a.value END) height,
MAX(Case WHEN a.name = 'width' THEN a.value END) width
FROM Product p
JOIN Product_Attribute pa ON p.product_id = pa.product_id
JOIN Attribute a ON pa.attribute_id = a.attribute_id
GROUP BY p.product_id
Good luck.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK