Help needed with MySQL query to join data spanning multiple tables with data used as column names

Question

I need a little help putting together a SQL query that will give me the following resultsets:

and

The data model looks like this:

The tricky part for me is that the columns to the right of the "Product" in the resultset aren't really columns in the database but rather key/value pairs spanned across the data model.

Table data is as follows:

My apologies in advance for the image heavy question and the image quality. This just seemed like the easiest way to convey the information. It'll probably take someone less time to write the query statement to achieve the results than it did for me to assemble this question.

By the way, the "product_option" table image is truncated but it illustrated the general idea of the data structure.

The MySQL server version is 5.1.45.

Answer 1

They're both standard pivot queries, but they have to be separate on account of different column headings and one has more columns than the other.

Query 1:

---

   SELECT p.name AS product,
          MAX(CASE WHEN o.name = 'Brand' THEN po.value ELSE NULL END) AS Brand,
          MAX(CASE WHEN o.name = 'Size' THEN po.value ELSE NULL END) AS Size,
          MAX(CASE WHEN o.name = 'Color' THEN po.value ELSE NULL END) AS Color,
          MAX(CASE WHEN o.name = 'Material' THEN po.value ELSE NULL END) AS Material,
          MAX(CASE WHEN o.name = 'Sole' THEN po.value ELSE NULL END) AS Sole
     FROM PRODUCT p
     JOIN CATEGORY c ON c.category_id = p.category_id
                    AND c.name = 'shoe'
LEFT JOIN PRODUCT_OPTION po ON po.product_id = p.product_id
LEFT JOIN OPTION o ON o.option_id = po.option_id
 GROUP BY p.name

Query 2:

---

   SELECT p.name AS product,
          MAX(CASE WHEN o.name = 'Make' THEN po.value ELSE NULL END) AS Make,
          MAX(CASE WHEN o.name = 'Model' THEN po.value ELSE NULL END) AS Model,
          MAX(CASE WHEN o.name = 'Color' THEN po.value ELSE NULL END) AS Color,
          MAX(CASE WHEN o.name = 'Doors' THEN po.value ELSE NULL END) AS Doors
     FROM PRODUCT p
     JOIN CATEGORY c ON c.category_id = p.category_id
                    AND c.name = 'car'
LEFT JOIN PRODUCT_OPTION po ON po.product_id = p.product_id
LEFT JOIN OPTION o ON o.option_id = po.option_id
 GROUP BY p.name

Answer 2

try:

select p.name as [Product]
       , MAX(select value from product_option where product_Id = p.product_id and and option_Id = 1) as Brand
       , MAX(select value from product_option where product_Id = p.product_id and and option_Id = 2) as Size
       , MAX(select value from product_option where product_Id = p.product_id and and option_Id = 3) as Color
       , MAX(select value from product_option where product_Id = p.product_id and and option_Id = 4) as Material
       , MAX(select value from product_option where product_Id = p.product_id and and option_Id = 4) as Sole
 from product p
GROUP BY p.name

Same principle for second table