我可以排除行在我的复杂查询条件是满足吗?(组合WHERE +多个AND) - can I EXCLUDE rows in my complex query where criteria is met? (combining WHERE + multi AND)

can I EXCLUDE rows in my complex query where criteria is met? (combining WHERE + multi AND)

我需要从表中获得结果，其中分组id存在于field1 = a和field2不为NULL的行中，但不是field1 = b和field2为NULL的行。只是语法错误，不知道如何结合这些条件…

这就是我要做的:

SELECT a.post_title AS title, a.id,
     MAX(CASE WHEN b.meta_key = 'endorser' THEN b.meta_value END) endorser,
     MAX(CASE WHEN b.meta_key = 'trail' THEN b.meta_value END) trail,
     MAX(CASE WHEN b.meta_key = 'townarea' THEN b.meta_value END) townarea,
     MAX(CASE WHEN b.meta_key = 'state' THEN b.meta_value END) state,
     MAX(CASE WHEN b.meta_key = 'start-date' THEN b.meta_value END) startdate,
     MAX(CASE WHEN b.meta_key = 'description' THEN b.meta_value END) description,
     MAX(CASE WHEN b.meta_key = 'organizer-name' THEN b.meta_value END) organizer,
     MAX(CASE WHEN b.meta_key = 'info-email' THEN b.meta_value END) infoemail
     FROM wp_posts a LEFT JOIN wp_postmeta b ON a.id = b.post_id
     WHERE b.post_id IN 
        (SELECT Post_id FROM wp_postmeta 
         WHERE (meta_key = 'endorser' AND meta_value IS NOT NULL) 
         AND (meta_key = 'trail' AND meta_value IS NULL)) group by b.post_id

和表看起来像这样:

meta_id  |   post_id   |  meta_key  |   meta_value
---------|-------------|------------|------------
  1      |     53      |  endorser  |   joe
  2      |     54      |  trail     |   trail name

所以我会得到包含post_id 53的行，但不是54

我很累，所以我可能错过了一些简单的，像一个简单的OUTER JOIN?

小提琴在这里:http://sqlfiddle.com/#!2/ee5420/1

您可以通过使用b.meta_key IN ('endorser','trail')和where子句来摆脱子查询，您可以使用CASE为您的is null和not null标准

SELECT a.post_title AS title, a.id,
(SELECT MAX(meta_value) FROM wp_postmeta WHERE meta_key = 'endorser' AND post_id =a.id) endorser,
(SELECT MAX(meta_value) FROM wp_postmeta WHERE meta_key = 'trail' AND post_id =a.id) trail,   
(SELECT MAX(meta_value) FROM wp_postmeta WHERE meta_key = 'townarea' AND post_id =a.id) townarea,   
(SELECT MAX(meta_value) FROM wp_postmeta WHERE meta_key = 'state' AND post_id =a.id) state,   
(SELECT MAX(meta_value) FROM wp_postmeta WHERE meta_key = 'start-date' AND post_id =a.id) startdate,   
(SELECT MAX(meta_value) FROM wp_postmeta WHERE meta_key = 'description' AND post_id =a.id) description,   
(SELECT MAX(meta_value) FROM wp_postmeta WHERE meta_key = 'organizer-name' AND post_id =a.id) organizer,   
(SELECT MAX(meta_value) FROM wp_postmeta WHERE meta_key = 'info-email' AND post_id =a.id) infoemail   
     FROM wp_posts a 
     LEFT JOIN wp_postmeta b ON a.id = b.post_id
 WHERE b.meta_key  IN ('endorser','trail')
     AND (
  CASE
    WHEN b.meta_key = 'endorser' 
    THEN b.meta_value IS NOT NULL 
    WHEN b.meta_key = 'trail' 
    THEN b.meta_value IS NULL 
  END
)
HAVING trail =''

小提琴演示

不要使用LEFT JOIN，使用INNER JOIN

FROM wp_posts a INNER JOIN wp_postmeta b ON a.id = b.post_id