Mysql查询、WP自定义字段

Question

我需要从几个 WP 表中选择一些东西，其中之一是具有特定键的自定义字段值。问题是，一些帖子没有带有此键的自定义字段，但除此之外它们匹配查询。所以问题是，我如何选择甚至没有带有此键的自定义字段但不是匹配查询的帖子？没有自定义字段的帖子的字段值应设置为 0。我认为它类似于 COALESCE，但我要么用错了，要么不能在这种情况下使用。到目前为止我的查询是:

select lists.id,lists.`type`,lists.`status`,lists.watched_eps, lists.score,wp_posts.post_title,wp_posts.post_name,wp_terms.slug,wp_postmeta.meta_value from anime_lists
INNER JOIN wp_posts ON (wp_posts.ID = lists.post_id ) 
INNER JOIN wp_postmeta ON (wp_postmeta.post_id= lists.post_id )
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
INNER JOIN wp_term_taxonomy ON(wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
INNER JOIN wp_terms ON(wp_terms.term_id = wp_term_taxonomy.term_id)
where lists.id=1
and wp_term_taxonomy.taxonomy = 'category'
and wp_postmeta.meta_key='eps'
group by lists.`post_id`
order by lists.`status`, wp_posts.post_title

实际上，查询输出我需要的所有内容，但没有这些没有自定义字段“eps”的帖子。如果我删除 和 wp_postmeta.meta_key='eps' 它会输出这些帖子，但这样我会在 wp_postmeta.meta_value 字段中得到错误的值。

Answer 1

据我所知，您只需将 wp_postmeta 连接更改为 LEFT JOIN 并将该表上的 WHERE 条件移动到连接，留下;

SELECT lists.id, lists.`type`, lists.`status`, lists.watched_eps, 
       lists.score,wp_posts.post_title,wp_posts.post_name,wp_terms.slug,
       wp_postmeta.meta_value 
FROM anime_lists
INNER JOIN wp_posts 
  ON wp_posts.ID = anime_lists.post_id
LEFT JOIN wp_postmeta 
  ON wp_postmeta.post_id = anime_lists.post_id 
 AND wp_postmeta.meta_key='eps'
INNER JOIN wp_term_relationships 
  ON (wp_posts.ID = wp_term_relationships.object_id) 
INNER JOIN wp_term_taxonomy 
  ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
INNER JOIN wp_terms 
  ON(wp_terms.term_id = wp_term_taxonomy.term_id)
WHERE lists.id=1
  AND wp_term_taxonomy.taxonomy = 'category'
GROUP BY lists.`post_id`
ORDER BY lists.`status`, wp_posts.post_title