I'm trying to use MySQL 8 to create a SELECT query that "creates" 5 new fields based on the results from 5 other queries, however, I'm struggling to work out how I get this to work.

Consider this scenario:

product_attributes

Now consider that my "user input" wants the first Attribute to be "Width" and the second attribute to be "Weight", i.e. att1 = Width, att2=Weight, att3..... etc

I wish to construct a SELECT query that returns the following:

I can get this to work with attribute 1, but I'm struggling to figure out how to make this work for attribute 2.

This is the query I have:

select `att1`,ATT_TABLE.productid FROM 
(select `attribute_value` as att1, `productid` from `product_attributes` where attribute_name='Width') ATT_TABLE 

JOIN products on ATT_TABLE.productid = products.productid 
WHERE products.catid='12345'

Ignore the JOIN/WHERE - that is just some extra logic I need to grab a particular categories products, but I have included it in case it would break any answers received.

How do I modify the query to include another sub query SELECT statement to get my second attribute, or is there another method to achieve the same?

Kind of like this:

select `att1`,`att2`,ATT_TABLE.productid FROM 
(select `attribute_value` as att1, `productid` from `product_attributes` where attribute_name='Width') ATT_TABLE 
(select `attribute_value` as att2, `productid` from `product_attributes` where attribute_name='Weight') ATT_TABLE2 

EDIT: An alternative suggestion by slashroot was to use CASE / GROUP BY.

select productid, 
CASE 
   WHEN attribute_name ='Width' THEN attribute_value 
END as att1,
CASE 
   WHEN attribute_name ='Weight' THEN attribute_value 
END as att2
FROM product_attributes
WHERE productid='1'
) 

However, this then returns multiple rows for the same productID and I'm struggling to figure out the GROUP BY statement. This is the return from that statement:

How do I get a group by to condense this down?

CodePudding user response：

That was a good start you did in your EDIT above. But you were missing a couple steps.

Have a look at this query, it should return the desired results

SELECT productid, 
MAX(CASE WHEN attribute_name = 'Width' THEN attribute_value ELSE NULL END) as att1,
MAX(CASE WHEN attribute_name = 'Weight' THEN attribute_value ELSE NULL END) as att2
FROM product_attributes
GROUP BY productid

Check the results from this DB FIDDLE