表 A 中的 MySQL 行，其 ID 来自查询，其中 ID 与表 B 中的一个条件匹配

Question

    table wp_posts    +-----+----------+    | ID  |post_type |    +-----+----------+    |  1  |        A |    |  2  |        A |    |  3  |        A |     |  3  |        B |     |  3  |        C |     +-----+----------+

    table wp_term_relationships (object_id = wp_posts.ID)    +-----------+------------------+    | object_id | term_taxonomy_id |    +-----------+------------------+    |  1        |                1 |    |  2        |                3 |    |  2        |                2 |    |  1        |                2 |    |  3        |                1 |     +-----------+-------------------+

    table wp_esp_datetime (EVT_ID = wp_posts.ID)    +-----------+---------------------+    | EVT_ID    | DTT_EVT_end         |    +-----------+---------------------+    |  1        | 2015-12-01 16:00:00 |    |  2        | 2015-12-01 16:00:00 |    |  3        | 2015-12-01 16:00:00 |    +-----------+---------------------+

Now what I want to do is:query all from wp_posts with left joined datetime from wp_esp_datetime, but only those who have no entry in wp_term_relationships with term_taxonomy_id = 3.My problem is, that the wp_post.ID can be multiple times in wp_term_relationships.object_id

My current query looks like this:

SELECT * FROM wp_posts 
INNER JOIN wp_term_relationships on wp_posts.ID = wp_term_relationships.object_id 
LEFT JOIN wp_esp_datetime on wp_posts.ID = wp_esp_datetime.EVT_ID
WHERE wp_posts.post_type = 'A' GROUP BY wp_posts.ID

所以问题是:

如何从 wp_posts 中排除 wp_term_relationships 中包含 term_taxonomy_id = 3 的条目的行，即使与另一个 term_taxonomy(不是 3)匹配？

Answer 1

添加(不)存在子句

SELECT * FROM wp_posts p
INNER JOIN wp_term_relationships tr on p.ID = tr.object_id 
LEFT JOIN wp_esp_datetime ed on p.ID = ed.EVT_ID 
WHERE p.post_type = 'A' 
and not exists (select null 
                from wp_term_relationships
                where term_taxonomy_id = 3
                and p.ID = object_id)
GROUP BY p.ID