Can I create an SQL query that joins two tables where one has key value pairs?-CodePudding

I have two tables (in Woo Commerce in WordPress) where I would like to merge the results

A simplified version of the tables are:

ID	Product title
1	Gadget
2	Gizmo

And

Meta_ID	Product_ID	Meta_key	Meta_value
1	1	stock	20
2	1	download	No
3	2	stock	12
4	2	download	yes

'ID' in the first table corresponds to the foreign key of 'Product_ID' in the second table.

Is there any way I can use a select query to merge these to produce the following result:

ID	Product_title	stock	download
1	Gadget	20	No
2	Gizmo	12	Yes

CodePudding user response：

The answer without pivot:

SELECT
    id,
    product_title,
    b.meta_value AS 'stock',
    c.meta_value AS 'download' 
FROM
    table1 AS a
    LEFT JOIN table2 AS b ON (a.id = b.product_id AND b.meta_key = 'stock' )
    LEFT JOIN table2 AS c ON (a.id = c.product_id AND c.meta_key = 'download')

CodePudding user response：

    SELECT product.id,
product.Product_title,
b.meta_value AS 'stock', 
c.meta_value AS 'download' 
FROM `product` 
LEFT JOIN meta ON product.id = meta.Product_ID 
LEFT JOIN meta AS b ON (product.id = b.product_id AND b.meta_key = 'stock' ) 
LEFT JOIN meta AS c ON (product.id = c.product_id AND c.meta_key = 'download') 
GROUP BY product.id;