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

Question

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

Does this answer your question? [How can I return pivot table output in MySQL?](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql) — astentx, Jul 29 '22 at 11:19

score 3 · Answer 1 · answered Jul 29 '22 at 11:25

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')

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

1 Answers1