MySQL ignores NOT EQUAL != in a SELECT anyone know why?

[servant]

Hi there,  experts!

 

Here is my lovely issue:

 

There are two tables involved:  simple_posts and simple_responses.   

 

What I am trying to do is this:  Select post_id from simple_posts,  where the user who is currently logged in has NOT responded.

 

The following code should,  according to my budding knowledge of SQL,  work:

 

 

SELECT SQL_NO_CACHE distinct r.post_id FROM simple_responses r, simple_posts p WHERE r.post_id = p.post_id AND p.post_time > $cuttime AND p.public = 1 AND r.user_id != $user_id AND p.post_deleted = 0;

 

--- but it doesn't. 

 

It COMPLETELY ignores: "r.user_id != '$user_id'" as if it wasn't there.  No error messages are thrown at all.

 

Here is the fun part.  I can select all the posts the user HAS responded to by channging "r.user_id != '$user_id'" into "r.user_id = '$user_id'" which wroks just fine.

 

The query does EVERYTHING else correctly but if I want it to select items which do NOT match,  then it just pretends the NOT EQUAL isn't there. 

 

I did try to figure this out by myself,  three days worth now,  but I give up.  Could someone smarter than me please help?

 

Thank you!

[servant]

I think I just figured out what I have done wrong, and it isn't the query's fault. It is doing what it is supposed to, but there is more data than I was thinking of.

 

Here is what I think now: The r.post_id has more than one value, since more than one person has responded.

 

So I say: Select the POST_ID from responses where $USER_ID isn't the person who made the response.

 

Let me illustrate:

 

POST_ID #1 has five responses:

 

USER_ID# 10

USER_ID# 11

USER_ID# 12

USER_ID# 13

USER_ID# 14

 

I told it to select a row from ANYBODY but USER_ID#12.

 

It still selects POST_ID#1 because USER_ID#14 ALSO answered!

 

I added a subselect, and restructured the query just a bit:

 

SELECT p.post_id FROM simple_posts p INNER JOIN simple_responses r ON p.post_id=r.post_id WHERE p.post_time > $cuttime AND p.post_deleted = 0 AND p.public = 1 AND (SELECT user_id FROM simple_responses WHERE post_id=r.post_id AND user_id=$user_id LIMIT 1) IS NULL;

 

... and now all is well.

 

Thank you for your help!