SQL查询不返回结果 [英] SQL Query not returning results

问题描述

我使用三种类型的复选框来过滤来自WordPress的帖子（这个问题是关于查询的），我知道一个事实，我应该返回结果与以下查询（我生成第7行自动根据选择什么在我的过滤器中）：

pre code $ SELECT $ FROM wp_posts
LEFT JOIN wp_postmeta ON（wp_posts.ID = wp_postmeta.post_id）
LEFT JOIN wp_term_relationships ON（wp_posts.ID = wp_term_relationships.object_id）
LEFT JOIN wp_term_taxonomy ON（wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id）
LEFT JOIN wp_terms ON（wp_terms.term_id = wp_term_taxonomy.term_id ）
WHERE
wp_terms.slug IN（'academia'）AND wp_terms.slug IN（'early-career'）
AND wp_term_taxonomy.taxonomy ='jobtype'
AND wp_posts。 post_status ='publish'
AND wp_posts.post_type ='jobs'
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC

我至少有5个职位在类别学术界和早期职业。

如果我将第7行更改为只有一个 IN 语句，它会过滤一个类别：

  wp_terms.slug IN（'academia'，'journalism'）//来自同一类别的多个
 AND wp_term_taxonomy.taxonomy ='jobtype '...... 
  

它会找到所有的工作，无论是学术界还是新闻界我期望的），但是当我添加其他 IN 语句时，没有任何东西会被返回（即使我的帖子与逻辑匹配）。 b

任何人都可以告诉我是否有错（可能是 IN 和值之间的空格），因为我一直在这天没有运气。

任何帮助非常感谢！谢谢！

PS 如果有人想看看这个网站（看看我的意思）： http://www.libertyguide.com/jobs

目前我有一个全球或，而不是任何 IN 的，直到我得到这个工作。

$在 IN 子句中指定多个值表示该行必须匹配其中的一个值。指定2 IN 子句指出该行必须匹配BOTH，这是不可能的（一个varchar不能同时是学术界和早期职业）。 / p>

尝试将以下内容添加到查询的末尾：

  HAVING COUNT（*）> [b 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
  SELECT * FROM wp_posts 
 LEFT JOIN wp_postmeta ON（wp_posts.ID = wp_postmeta.post_id）
 LEFT JOIN wp_term_relationships ON（wp_posts.ID = wp_term_relationships.object_id ）
 LEFT JOIN wp_term_taxonomy ON（wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id）
 LEFT JOIN wp_terms ON（wp_terms.term_id = wp_term_taxonomy.term_id）
 WHERE 
 wp_terms.slug IN（'学术界，早期职业）
和wp_term_taxonomy.taxonomy ='jobtype'
 AND wp_posts.post_status ='publish'
 AND wp_posts.post_type ='jobs'
 GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC 
 HAVING COUNT（*）= 2 
  
 
I am using three categories of checkboxes to filter posts from WordPress (this question is about the query), and I know for a fact I should be returning results with the following query (I generate the line 7 automatically based on what's selected in my filter):
SELECT * FROM wp_posts 
LEFT JOIN wp_postmeta ON(wp_posts.ID = wp_postmeta.post_id) 
LEFT JOIN wp_term_relationships ON(wp_posts.ID = wp_term_relationships.object_id) 
LEFT JOIN wp_term_taxonomy ON(wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) 
LEFT JOIN wp_terms ON(wp_terms.term_id = wp_term_taxonomy.term_id) 
WHERE 
    wp_terms.slug IN ('academia') AND wp_terms.slug IN ('early-career') 
    AND wp_term_taxonomy.taxonomy = 'jobtype' 
    AND wp_posts.post_status = 'publish' 
    AND wp_posts.post_type = 'jobs' 
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC
I have at least 5 posts that have are in the category academia AND early-career.

If I change line 7 to just have one IN statement, it filters that one category just fine:
wp_terms.slug IN ('academia','journalism') //More than one from same category
AND wp_term_taxonomy.taxonomy = 'jobtype'......
It'll find all jobs that are either academia OR journalism (exactly what I expect), but when I add in the other IN statement, nothing is returned (even though I have posts that match the logic).

Can anyone tell me if something is wrong (maybe the spaces between the IN and the values?), because I've been at this for days with no luck.

Any help is greatly appreciated! Thanks!

P.S. If anyone wants to take a look at the site this is on (for looking at what I mean): http://www.libertyguide.com/jobs

Currently I have a global OR instead of any IN's until I get this to work.
解决方案
Specifying multiple values in the IN clause states the row must match ONE of those values. Specifying 2 IN clauses states that the row must match BOTH, which is impossible (a varchar cannot be both 'academia' and 'early-career' at the same time).

Try adding the following to the end of your query:
HAVING COUNT(*) > [NUMBER OF VALUES SELECTED]
So it becomes:
SELECT * FROM wp_posts 
LEFT JOIN wp_postmeta ON(wp_posts.ID = wp_postmeta.post_id) 
LEFT JOIN wp_term_relationships ON(wp_posts.ID = wp_term_relationships.object_id) 
LEFT JOIN wp_term_taxonomy ON(wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) 
LEFT JOIN wp_terms ON(wp_terms.term_id = wp_term_taxonomy.term_id) 
WHERE 
    wp_terms.slug IN ('academia', 'early-career') 
    AND wp_term_taxonomy.taxonomy = 'jobtype' 
    AND wp_posts.post_status = 'publish' 
    AND wp_posts.post_type = 'jobs' 
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC
HAVING COUNT(*) = 2


                        
这篇关于SQL查询不返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！