Product

Category

Item

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

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?

CodePudding user response：

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'

CodePudding user response：

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;