Get Data From Multiple Tables Using SQL

Here’s how to get data from multiple tables using SQL with an example using WordPress post meta_key and meta_value pairs.

SELECT P.ID, P.post_title AS name,
    MAX(IF(PM.meta_key = 'profile_date_of_birth', PM.meta_value, NULL)) AS DoB,
    MAX(IF(PM.meta_key = 'profile_biography', PM.meta_value, NULL)) AS Biography,
    MAX(IF(PM.meta_key = 'membership_membership_renewal', PM.meta_value, NULL)) AS RenewalDate
FROM wp_posts AS P
LEFT JOIN wp_postmeta AS PM on PM.post_id = P.ID
WHERE P.post_type = 'post'
GROUP BY P.ID
ORDER BY P.post_date DESC

How To Display Multiple Post Meta_Key/Meta_Value Pairs Using SQL

Many themes and plugins store associated post data in the wp_postmeta table. That’s why the table is there.

However, the WordPress function get_post_meta() only retrieves a single meta key’s data. If you want to get data from multiple tables and retrieve a whole bunch of meta_keys, that’s a lot of single database requests which could slow down your function.

You can use the above SQL code to retrieve multiple meta_key values simultaneously.

In line 6, you make a table join between the wp_post and the wp_postmeta tables using the post ID as the primary key.

Lines 2-4 contain the magic of pivoting each meta_key result from a row to a column.

Don’t omit line 8. You will need to group the results; otherwise, you’ll get an SQL error.

This works great for exporting data for client reports without using a plugin like WP All Import. It also works for any post type or post meta, so you could use it for WooCommerce orders, user and product data.