Question 112976 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Pivot and Left Join

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    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;

  2. 0

    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'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search