MySQL display rows where value IS NULL or equal to X
Solution 1
Try to use parenthasis on OR
condition (userContests.value = 0 OR 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
Solution 2
You partially answered your own question in the title: OR not AND, but try using 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
Solution 3
(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.
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?
Sweepster
Updated on January 09, 2020Comments
-
Sweepster over 4 years
userPosts.value can contain one of two values: 0 or 1.
I am Left Joining userPosts to my Posts table.
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).
The following only get me posts where value = 0 but no 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:
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!
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?