Merge two separate mysql (wordpress) queries-CodePudding

I have two separate mysql queries in wordpress that i want to merge into one.

First mysql query, returns all posts titles from a specific CPT called wpbdp_listing with some meta values:

SELECT iZYfXxF_posts.ID, iZYfXxF_posts.post_title, iZYfXxF_postmeta.meta_key, iZYfXxF_postmeta.meta_value
    FROM iZYfXxF_posts, iZYfXxF_postmeta
    WHERE iZYfXxF_posts.ID = iZYfXxF_postmeta.post_ID
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpml_word_count'
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpbdp[access_key'
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_edit_lock'
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_edit_last'
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpml_media_featured'
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpml_media_duplicate'
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_alp_processed'
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpml_location_migration_done'
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpuf_form_id'
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpuf_lock_editing_post'
    AND iZYfXxF_postmeta.meta_key NOT LIKE 'rs_page_bg_color'
     AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpbdp[name]'
      AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpml_media_has_media'
       AND iZYfXxF_postmeta.meta_key NOT LIKE 'rs_page_bg_color'
          AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpbdp[access_key]'
               AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpbdp[fields][12]'
                AND iZYfXxF_postmeta.meta_key NOT LIKE '_sub_allowed_term_ids'
                 AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpuf_res_display'
                  AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpuf_res_display'
                   AND iZYfXxF_postmeta.meta_key NOT LIKE '_elementor_data' 
                      AND iZYfXxF_postmeta.meta_key NOT LIKE '_frontend_admin_version'      
    AND iZYfXxF_posts.post_type='wpbdp_listing'
           AND iZYfXxF_posts.post_status = 'publish';

Second mysql query, returns all posts titles from a specific CPT wpbdp_listing and taxonomy names:

 SELECT iZYfXxF_posts.post_title AS ‘Title’, iZYfXxF_terms.name AS ‘Cateogry’
 FROM iZYfXxF_posts
 INNER JOIN iZYfXxF_term_relationships
ON iZYfXxF_posts.ID = iZYfXxF_term_relationships.object_id
INNER JOIN iZYfXxF_terms
 ON iZYfXxF_term_relationships.term_taxonomy_id = iZYfXxF_terms.term_id
INNER JOIN iZYfXxF_term_taxonomy
ON iZYfXxF_term_relationships.term_taxonomy_id = iZYfXxF_term_taxonomy.term_taxonomy_id
WHERE iZYfXxF_posts.post_status = 'publish'
AND iZYfXxF_posts.post_type = 'wpbdp_listing'
AND iZYfXxF_term_taxonomy.taxonomy = 'wpbdp_category'
ORDER BY iZYfXxF_terms.name;

Both mysql queries work correct in phpmyadmin.

I want to merge those two into one that will return four collumns - First collumn post title , Second/third collumn meta_key _ meta_value , and fourth collumn the taxonomy name.

I have tried the following but not working:

#1054 - Unknown column 'iZYfXxF_posts.ID' in 'on clause'

 SELECT iZYfXxF_posts.ID,iZYfXxF_posts.post_title AS ‘Title’, iZYfXxF_postmeta.meta_key,               iZYfXxF_postmeta.meta_value ,iZYfXxF_terms.name AS ‘Cateogry’ 
    FROM iZYfXxF_posts, iZYfXxF_postmeta 
   INNER JOIN iZYfXxF_term_relationships 
   ON iZYfXxF_posts.ID = iZYfXxF_term_relationships.object_id
   INNER JOIN iZYfXxF_terms
  ON iZYfXxF_term_relationships.term_taxonomy_id = iZYfXxF_terms.term_id
 INNER JOIN iZYfXxF_term_taxonomy
 ON iZYfXxF_term_relationships.term_taxonomy_id = iZYfXxF_term_taxonomy.term_taxonomy_id
  
   WHERE iZYfXxF_posts.ID = iZYfXxF_postmeta.post_ID
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpml_word_count'
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpbdp[access_key'
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_edit_lock'
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_edit_last'
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpml_media_featured'
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpml_media_duplicate'
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_alp_processed'
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpml_location_migration_done'
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpuf_form_id'
    AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpuf_lock_editing_post'
    AND iZYfXxF_postmeta.meta_key NOT LIKE 'rs_page_bg_color'
     AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpbdp[name]'
      AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpml_media_has_media'
       AND iZYfXxF_postmeta.meta_key NOT LIKE 'rs_page_bg_color'
          AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpbdp[access_key]'
               AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpbdp[fields][12]'
                AND iZYfXxF_postmeta.meta_key NOT LIKE '_sub_allowed_term_ids'
                 AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpuf_res_display'
                  AND iZYfXxF_postmeta.meta_key NOT LIKE '_wpuf_res_display'
                   AND iZYfXxF_postmeta.meta_key NOT LIKE '_elementor_data' 
                      AND iZYfXxF_postmeta.meta_key NOT LIKE '_frontend_admin_version'      
    AND iZYfXxF_posts.post_type='wpbdp_listing'
    AND iZYfXxF_term_taxonomy.taxonomy = 'wpbdp_category'
           AND iZYfXxF_posts.post_status = 'publish';

First Query return this: ibb.co/WgvFptD Also second query return this ibb.co/7Qtsxvb What i want is to add last collumn (category) in the first query , so we will also have the taxonomy/category of the post.

Any idea? what can i do to first two first queries into one? any help?

CodePudding user response：

This query should do the job:

SELECT posts.ID,
       posts.post_title AS ‘Title’,
       postmeta.meta_key,
       postmeta.meta_value,
       terms.name       AS ‘Cateogry’
FROM iZYfXxF_posts posts
INNER JOIN iZYfXxF_postmeta postmeta ON posts.ID = postmeta.post_ID
INNER JOIN iZYfXxF_term_relationships term_relationships ON posts.ID = term_relationships.object_id
INNER JOIN iZYfXxF_terms terms ON term_relationships.term_taxonomy_id = terms.term_id
INNER JOIN iZYfXxF_term_taxonomy term_taxonomy ON term_relationships.term_taxonomy_id = term_taxonomy.term_taxonomy_id
WHERE postmeta.meta_key NOT IN
      ('_wpml_word_count', '_wpbdp[access_key', '_edit_lock', '_edit_last', '_wpml_media_featured',
       '_wpml_media_duplicate', '_alp_processed', '_wpml_location_migration_done', '_wpuf_form_id',
       '_wpuf_lock_editing_post', 'rs_page_bg_color', '_wpbdp[name]', '_wpml_media_has_media', 'rs_page_bg_color',
       '_wpbdp[access_key]', '_wpbdp[fields][12]', '_sub_allowed_term_ids', '_wpuf_res_display', '_wpuf_res_display',
       '_elementor_data', '_frontend_admin_version')
  AND posts.post_type = 'wpbdp_listing'
  AND term_taxonomy.taxonomy = 'wpbdp_category'
  AND posts.post_status = 'publish'