where子句for等于一个字段，不等于另一个 [英] Where clause for equals a field, not equal another

问题描述

那是那些日子之一，我只是想不通.

It's been one of those days and I just can't figure this out.

我有以下查询:

SELECT * FROM wp_posts
JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_posts.ID
WHERE term_taxonomy_id = 80

很明显，这会选择类别ID为80的所有帖子.我需要做的是选择类别80但不是类别109的所有帖子.

Obviously, this selects all of the posts that are in the category ID 80. What I need to do is select all the posts that are in category 80 but NOT in category 109.

我已经尝试过了，但是它只是从类别80中选择了相同的帖子.

I've tried this, but it just selects the same posts from category 80.

SELECT * FROM wp_posts
JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_posts.ID
WHERE term_taxonomy_id = 80
AND term_taxonomy_id = 109

这是表格结构:

wp_posts

|    ID    |
------------
|    1     |
|    2     |

wp_term_relationships

| object_id | term_taxonomy_id |
|-----------|------------------|
|     1     |       80         |
|     2     |       80         |
|     1     |       109        |

object_id与post_id匹配

object_id matches to post_id

查询应仅返回ID 2，因为ID 1在80和109中.

The query should only return ID 2 since ID 1 is in both 80 and 109.

我知道我已经做了一百万遍了，但是我一生都无法让它起作用.有帮助吗?

I know I've done this a million times but I just can't for the life of me get this to work. Any help?

推荐答案

您可以编写:

SELECT *
  FROM wp_posts
  JOIN wp_term_relationships
    ON wp_term_relationships.object_id = wp_posts.ID
 WHERE term_taxonomy_id = 80
   AND wp_posts.id NOT IN
        ( SELECT object_id
            FROM wp_term_relationships
           WHERE term_taxonomy_id = 109
        )
;

(请参见§ 13.2.10.3" MySQL 5.6参考手册 中带有ANY，IN或SOME的子查询.)

(See § 13.2.10.3 "Subqueries with ANY, IN, or SOME" in the MySQL 5.6 Reference Manual.)

这篇关于where子句for等于一个字段，不等于另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！