In a RDMS with the following query I try to get postmeta.meta_value of the post with custom post type points
. To identify which key they belong to I made a case when. This query returns a greater number of records than the number of custom post types in most cases full of null. What I expect is a result that contains the same number of custom post type points. Could you please tell me the correct way to do this?
SELECT
case when wp_postmeta.meta_key = 'zone' then wp_postmeta.meta_value end 'zone',
case when wp_postmeta.meta_key = 'category' then wp_postmeta.meta_value end 'category',
case when wp_postmeta.meta_key = 'country' then wp_postmeta.meta_value end 'country'
FROM
wp_posts
INNER JOIN
wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
WHERE
wp_posts.post_type = 'points'
I hope a result that can look like this
+----------------------------------+---------+------------------------+
| zone | cate | country |
+----------------------------------+---------+------------------------+
| Zone 1 | Cat 1 | Country 1 |
| Zone 2 | Cat 2 | Country 2 |
| Zone 1 | Cat 3 | Country 3 |
+----------------------------------+---------+------------------------+
I do not know if this is relevante but this postmeta key/values area created by custom fields created by Advanced custom fields plugin
get_post_meta( $post_id )
. I would advise against using raw SQL to query that table though, and that querying for posts via their post meta is super slow and not what that table was designed for ( it's designed for finding key/value pairs when you already know the post ID, taxonomies and terms are for filtering/searching ) – Tom J Nowell♦ Dec 5 '18 at 16:44