MySQL displays rows where IS is NULL or equal to X

Question

userPosts.value can contain one of two values: 0 or 1.

I left joining userPosts to my message table.

I want to get all posts from my Posts table, where userPosts.value = 0, as well as all posts that have no userPosts.value at all (thus NULL).

The following are only messages where value = 0 but not 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 messages only receive messages 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 gives no results:

 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 calls my messages with value = 0, as well as NULL, but all my NULL messages are repeated 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?

Answer 1

Try using a parentase in the OR state (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 

Answer 2

(x = 0) AND (x is NULL) - one field cannot be two values ​​at the same time. It is not surprising that you will not get any results because you indicated a condition that cannot be satisfied.

As for the rest of the request. You use $wdpd->posts as the source table, but then use a table called userContests in the where clause. Does $ wpdb-> allow a message to userContests? If so, why make a dynamic table name in one place and hard-code it in another?

Answer 3

You partially answered your question in the header: 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