Get 2 meta values from meta key column

Question

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.

I want to select 'name', 'SKU' and 'price' from 2 WordPress tables, namely 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'"

Answer 1

It sounds like you could do with a join so you're relating the meta information to the right posts.

SELECT
  post.post_title,
  meta.meta_value
FROM wp_posts AS post
LEFT JOIN wp_postmeta AS meta
  ON post.post_id = meta.post_id
WHERE post.post_type = 'product'
  AND meta.meta_key IN ('_sku', '_price')

Example results:

post_title    | meta_value
--------------|-----------
Cheddar       | CHE001
Cheddar       | 2.45
Red Leicester | CHE002
...

This assumes that the id column in wp_posts is post_id.

It's important to note that this will return up to two rows for each post, depending on whether it has a meta row for _sku and _price). If you need the data all on the same row (as you might for your export) you might need something like this instead:

SELECT
  post.post_title,
  metaSku.meta_value AS sku,
  metaPrice.meta_value AS price
FROM wp_posts AS post
LEFT JOIN (
  SELECT
    *
  FROM wp_postmeta
  WHERE meta_key = '_sku'
) AS metaSku
  ON post.post_id = metaSku.post_id
LEFT JOIN (
  SELECT
    *
  FROM wp_postmeta
  WHERE meta_key = '_price'
) AS metaPrice
  ON post.post_id = metaPrice.post_id
WHERE post.post_type = 'product'

Example results:

post_title    | sku    | price
--------------|--------|------
Cheddar       | CHE001 | 2.45
Red Leicester | CHE002 |
...

I hope this helps.

Answer 2

This will works for me

SELECT post.post_title, metaSku.meta_value AS sku, metaPrice.meta_value AS price FROM wp_posts AS post LEFT JOIN ( SELECT * FROM wp_postmeta WHERE meta_key = '_sku' ) AS metaSku ON post.ID = metaSku.post_id LEFT JOIN ( SELECT * FROM wp_postmeta WHERE meta_key = '_price' ) AS metaPrice ON post.ID = metaPrice.post_id WHERE post.post_type = 'product';