如果(item_id, property_type)被定义为UNIQUE（这是合理的），那么连接2个Item表副本会更简单，甚至可能更快：

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'

SELECT
    item_id,
    fk_prod_id,
    fk_cat_id,
    prod_name,
    cat_name
FROM (
    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
    GROUP BY item_id
) AS t1 
LEFT JOIN product AS p ON p.prod_id = t1.fk_prod_id
LEFT JOIN category AS c ON c.cat_id = t1.fk_cat_id;