Here’s how to get data from multiple tables using SQL with an example using WordPress post 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.