userPosts.value can contain one of two values: 0 or 1.
userPosts.value可以包含两个值之一:0或1。
I am Left Joining userPosts to my Posts table.
我正在将userPosts加入我的帖子表。
I want to get all posts from my Posts table where userPosts.value = 0 as well as all posts that do not have any userPosts.value at all (thus, NULL).
我想从我的Posts表中获取userPosts.value = 0的所有帖子以及根本没有任何userPosts.value的所有帖子(因此,NULL)。
The following only get me posts where value = 0 but no NULL:
以下只给我发布值为0但没有NULL的帖子:
SELECT * FROM $wpdb->posts
LEFT JOIN userPosts ON ($wpdb->posts.ID = userPosts.postID)
WHERE userPosts.value != 1
ORDER BY $wpdb->posts.post_date DESC
The following only gets me posts where value = NULL:
以下只获取值为NULL的帖子:
SELECT * FROM $wpdb->posts
LEFT JOIN userPosts ON ($wpdb->posts.ID = userPosts.postID)
WHERE userPosts.value IS NULL
ORDER BY $wpdb->posts.post_date DESC
but this yields no results at all:
但这根本不会产生任何结果:
SELECT * FROM $wpdb->posts
LEFT JOIN userPosts ON ($wpdb->posts.ID = userPosts.postID)
WHERE userPosts.value = 0
AND userPosts.value IS NULL
ORDER BY $wpdb->posts.post_date DESC
and this does get me posts with value = 0 as well as NULL but it repeats all my NULL posts three times!
这确实得到了我的帖子值为0以及NULL,但它重复我的所有空白帖子三次!
SELECT * FROM $wpdb->posts
LEFT JOIN userPosts ON ($wpdb->posts.ID = userPosts.postID)
WHERE userPosts.value = 0
OR userPosts.value IS NULL
ORDER BY $wpdb->posts.post_date DESC
So what am I doing wrong?
那么我做错了什么?
3 个解决方案
#1
17
Try to use parenthasis on OR
condition (userContests.value = 0 OR userContests.value IS NULL)
尝试在OR条件上使用parenthasis(userContests.value = 0或userContests.value IS NULL)
SELECT * FROM $wpdb->posts
LEFT JOIN userPosts ON ($wpdb->posts.ID = userPosts.postID)
WHERE (userContests.value = 0
OR userContests.value IS NULL)
ORDER BY $wpdb->posts.post_date DESC
#2
1
(x = 0) AND (x is NULL)
- a single field cannot be two values at the same time. No surprise that you get no results at all, because you've specified a where condition that is impossible to satisfy.
(x = 0)AND(x为NULL) - 单个字段不能同时为两个值。毫无疑问,你根本没有得到任何结果,因为你已经指定了一个无法满足的条件。
As for the rest of the query. You use $wdpd->posts
as your source table, but then use a table named userContests
in the where clause. Does $wpdb->posts resolve to userContests? If so, why make the table name dynamic in one place and hard-coded it in another?
至于其余的查询。您使用$ wdpd-> posts作为源表,但随后在where子句中使用名为userContests的表。 $ wpdb->帖子是否解析为userContests?如果是这样,为什么要在一个地方使表名动态并在另一个地方硬编码呢?
#3
1
You partially answered your own question in the title: OR not AND, but try using DISTINCT
:
您在标题中部分回答了您自己的问题:或者不是AND,但尝试使用DISTINCT:
SELECT DISTINCT * FROM $wpdb->posts -- Note "DISTINCT"
LEFT JOIN userPosts ON ($wpdb->posts.ID = userPosts.postID)
WHERE userContests.value = 0
OR userContests.value IS NULL -- Note "OR"
ORDER BY $wpdb->posts.post_date DESC
#1
17
Try to use parenthasis on OR
condition (userContests.value = 0 OR userContests.value IS NULL)
尝试在OR条件上使用parenthasis(userContests.value = 0或userContests.value IS NULL)
SELECT * FROM $wpdb->posts
LEFT JOIN userPosts ON ($wpdb->posts.ID = userPosts.postID)
WHERE (userContests.value = 0
OR userContests.value IS NULL)
ORDER BY $wpdb->posts.post_date DESC
#2
1
(x = 0) AND (x is NULL)
- a single field cannot be two values at the same time. No surprise that you get no results at all, because you've specified a where condition that is impossible to satisfy.
(x = 0)AND(x为NULL) - 单个字段不能同时为两个值。毫无疑问,你根本没有得到任何结果,因为你已经指定了一个无法满足的条件。
As for the rest of the query. You use $wdpd->posts
as your source table, but then use a table named userContests
in the where clause. Does $wpdb->posts resolve to userContests? If so, why make the table name dynamic in one place and hard-coded it in another?
至于其余的查询。您使用$ wdpd-> posts作为源表,但随后在where子句中使用名为userContests的表。 $ wpdb->帖子是否解析为userContests?如果是这样,为什么要在一个地方使表名动态并在另一个地方硬编码呢?
#3
1
You partially answered your own question in the title: OR not AND, but try using DISTINCT
:
您在标题中部分回答了您自己的问题:或者不是AND,但尝试使用DISTINCT:
SELECT DISTINCT * FROM $wpdb->posts -- Note "DISTINCT"
LEFT JOIN userPosts ON ($wpdb->posts.ID = userPosts.postID)
WHERE userContests.value = 0
OR userContests.value IS NULL -- Note "OR"
ORDER BY $wpdb->posts.post_date DESC