MySQL Pivot and Left Join

Question

Product

prod_id	prod_name
10	Orange
11	Apple
12	Carrot
13	Lettuce

Category

cat_id	cat_name
20	Fruit
21	Vegetable

Item

item_id	property_type	property_value
30	fk_prod_id	10
30	fk_cat_id	20
31	fk_prod_id	11
31	fk_cat_id	20
32	fk_prod_id	12
32	fk_cat_id	21

I am trying to pivot Item then left join with Product and Category to get:

item_id	fk_prod_id	fk_cat_id	prod_name	cat_name
30	10	20	Orange	Fruit
31	11	20	Apple	Fruit
32	12	21	Carrot	Vegetable

Unfortunately:

SELECT
    item_id,
    MAX(CASE WHEN property_type = 'fk_prod_id' THEN property_value END) AS fk_prod_id,
    MAX(CASE WHEN property_type = 'fk_cat_id' THEN property_value END) AS fk_cat_id
FROM item AS i
LEFT JOIN product AS p ON p.prod_id = fk_prod_id
LEFT JOIN category AS c ON c.cat_id = fk_cat_id
GROUP BY item_id;

#Error Code: 1054. Unknown column 'fk_prod_id' in 'on clause'

How do I left join other table(s) after a pivot table for the above scenario?

Answer 1

If (item_id, property_type) is defined as UNIQUE (which is reasonable) then joining 2 Item tables copies is more simple and maybe even more fast:

SELECT item_id, 
       i1.property_value fk_prod_id, 
       i2.property_value fk_cat_id, 
       p.prod_name, 
       c.cat_name
FROM Item i1
JOIN Item i2 USING (item_id)
JOIN Product p ON p.prod_id = i1.property_value
JOIN Category c ON c.cat_id = i2.property_value
WHERE i1.property_type = 'fk_prod_id'
  AND i2.property_type = 'fk_cat_id'