CODEWITHSUNDEEP
mysqlsqlmapping
J.Corby
J.Corby

Reputation: 25

Column names based on a mapping table on mysql

I am very new to mysql. I have 2 tables.

Table 1:product_details

manufacturerID| sku | image1                   | image2                   | image3
--------------------------------------------------------------------------------------------------
     123      |  1  | image1-sku1-filename.jpg | image2-sku1-filename.jpg | image3-sku1-filename.jpg    
     123      |  2  | image1-sku2-filename.jpg | image2-sku2-filename.jpg | image3-sku2-filename.jpg    
     123      |  3  | image1-sku3-filename.jpg | image2-sku3-filename.jpg | image3-sku3-filename.jpg
     456      | 10  | image1-sku10-filename.jpg| image2-sku10-filename.jpg| image3-sku10-filename.jpg
     456      | 20  | image1-sku20-filename.jpg| image2-sku20-filename.jpg| image3-sku20-filename.jpg

Table 2:image_mapping

manufacturerID | image_column | image_type
-------------------------------------------
    123        | image3       | master
    123        | image1       | extra
    123        | image2       | extra
    456        | image3       | master

I need a SELECT query which maps all the products (sku) with product images (will replace image column names in Table 2) per manufacturerID and per sku.

Table 3: product_images

manufacturerID | sku | product_image            | image_type
--------------------------------------------------------------
    123        |  1  | image3-sku1-filename.jpg | master
    123        |  1  | image1-sku1-filename.jpg | extra
    123        |  1  | image2-sku1-filename.jpg | extra
    123        |  2  | image3-sku2-filename.jpg | master
    123        |  2  | image1-sku2-filename.jpg | extra
    123        |  2  | image2-sku2-filename.jpg | extra
    123        |  3  | image3-sku3-filename.jpg | master
    123        |  3  | image1-sku3-filename.jpg | extra
    123        |  3  | image2-sku3-filename.jpg | extra
    456        | 10  | image3-sku10-filename.jpg| master    
    456        | 20  | image3-sku20-filename.jpg| master

Upvotes: 1

Views: 1056

Answers (1)

Stidgeon
Stidgeon

Reputation: 2723

The following works for me with your data:

SELECT pd.manufacturerID, sku, 
       CASE WHEN im.image_column = 'image1' THEN pd.image1 
            WHEN im.image_column = 'image2' THEN pd.image2
            WHEN im.image_column = 'image3' THEN pd.image3 
            END AS prod_image, im.image_type
FROM product_details pd
JOIN image_mapping im
ON pd.manufacturerID = im.manufacturerID;

SQLFiddle: http://sqlfiddle.com/#!9/76fc1/3/0

Upvotes: 1

Related Questions