wordpress phpadmin SQL database query to get results in columns

Question

In my wordpress website I have multiple custom fields under post type 'home_decor', using the below query returns two field 'post id' and 'hd_product_description'. Currently, I am using this query to export fields one by one and then vlookup by post id. I have 7 fields in total, can someone help me with the SQL query that will return all seven columns from wp_postmeta. I have to export data from time to time and its become a long process.

SELECT * FROM `wp_postmeta` WHERE `meta_key` = 'product_description'

post id   product_description   retail_price   sale_price   product_sku   product_name
-------   -------------------   ------------   ----------   -----------   ------------
1245      about the product     $125           $115         1245alt       furniture

Answer 1

Your problem is to retrieve multiple attributes for your posts from your wp_postmeta table. It's known as an entity - attribute - value table in the language of database design. It's a notorious pain in the xxx neck to use, but it is extensible.

You need to join the postmeta table multiple times for this, one time for each named attribute you want. Here is the pattern.

SELECT wp_posts.ID post_id,
       product_description.meta_value product_description,
       retail_price.meta_value retail_price
       sale_price.meta_value sale_price

  /* always start with wp_posts, because some attibutes might be missing */
  FROM wp_posts

  /* left join wp_postmeta giving it an alias. put meta_key into ON clause */
  LEFT JOIN wp_postmeta product_description
           ON product_description.post_id = wp_posts.ID
          AND product_description.meta_key = 'product_description'

  /* and again for the next attribute */
  LEFT JOIN wp_postmeta retail_price 
           ON retail_price.post_id = wp_posts.ID
          AND retail_price.meta_key = 'retail_price'

  /* and again */
  LEFT JOIN wp_postmeta sale_price 
           ON sale_price.post_id = wp_posts.ID
          AND sale_price.meta_key = 'sale_price'

The trick is to use a separate LEFT JOIN to the wp_postmeta table for every attribute. For each of those LEFT JOIN operations, give the table an alias that's unique. For example LEFT JOIN wp_postmeta sale_price assigns the alias sale_price to this particular use of wp_postmeta.

Then, for each column in your SELECT clause, mention the alias and meta_value, then assign an alias to the column. For example, sale_price.meta_value sale_price.

Why LEFT JOIN instead of ordinary inner JOIN? If you used inner JOIN, your result set would only contain rows for posts that have every attribute you want. With LEFT JOIN you'll get NULL values for missing attributes. That is much more useful unless your data is absolutely perfect. (Umm, not much real world data is so perfect.)