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
SQL: Multiple meta_key and meta_value pairs

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.

Was this article helpful?
YesNo