query - Get 2 meta values from meta key column

I want to select 'name', 'SKU' and 'price' from 2 WP tables: wp_posts and wp_postmeta. I don't know how to get data from 'meta_value' column twice for 'meta_key'='_price' and 'meta_key'='_sku' , ex.

My current query:

                    "SELECT a.post_title, m1.meta_value, m2.meta_value FROM wp_posts a, wp_postmeta m1, wp_postmeta m2
                WHERE a.post_type='product' AND m1.post_id = a.ID
                AND m1.meta_key='_sku'
                AND m2.meta_key='_price'"

I want to select 'name', 'SKU' and 'price' from 2 WP tables: wp_posts and wp_postmeta. I don't know how to get data from 'meta_value' column twice for 'meta_key'='_price' and 'meta_key'='_sku' , ex.

My current query:

                    "SELECT a.post_title, m1.meta_value, m2.meta_value FROM wp_posts a, wp_postmeta m1, wp_postmeta m2
                WHERE a.post_type='product' AND m1.post_id = a.ID
                AND m1.meta_key='_sku'
                AND m2.meta_key='_price'"

• query
• mysql

Share Improve this question asked Feb 18, 2020 at 22:26 marcin łyczkomarcin łyczko 111 bronze badge 3

• 1 Is there a particular reason you're getting these values this way? The proper way to get a WooCommece product is wc_get_product(). – Jacob Peattie Commented Feb 19, 2020 at 0:08
• I need to use some library to export product names, SKU and prices to one CSV file. This library connects using PDO and needs an SQL query. – marcin łyczko Commented Feb 19, 2020 at 7:22
• 1 In that case, you'd probably be better off asking your question over at Stackoverflow and looking at the MySQL UNION clause. – TomC Commented Feb 19, 2020 at 7:39

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Not tested 100% but this might work:

SELECT a.post_title, m1.meta_value AS SKU, m2.meta_value AS PRICE
FROM wp_posts a
LEFT JOIN wp_postmeta m1
    ON a.ID = m1.post_id
LEFT JOIN wp_postmeta m2
    ON a.ID = m2.post_id
WHERE a.post_type='product' 
AND m1.meta_key='_sku'
AND m2.meta_key='_price'

Note that _price field corresponds to the "sale price" (if existing) so you may grab also the "list price":

SELECT a.post_title, m1.meta_value AS SKU, m2.meta_value AS SALE_PRICE, m3.meta_value AS LIST_PRICE
FROM wp_posts a
LEFT JOIN wp_postmeta m1
    ON a.ID = m1.post_id
LEFT JOIN wp_postmeta m2
    ON a.ID = m2.post_id
LEFT JOIN wp_postmeta m3
    ON a.ID = m3.post_id
WHERE a.post_type='product' 
AND m1.meta_key='_sku'
AND m2.meta_key='_price'
AND m3.meta_key='_regular_price'

---