MySQL ignoring NOT EQUAL != in query and 2 days later I don't know why! Help?

servant

Hello everyone!

I am new and this is just way out of my league at the moment:

$sql = "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'";

The idea is to show posts that the logged in user hasn't already responded to. So, without "r.user_id != '$user_id'" it returns every post that meets the other constraints.

Everything works just fine except the "!= '$user_id'" portion is completely ignored.

I have spent the last two days trying to figure this out on my own and I have hit the point where I know for certain that I need some help from someone smarter than me.

If that is you, could you tell me what I am doing wrong here?

I know it is ONLY that portion that is failing because I can change it from != to = and it does EXACTLY what it is supposed to do.

As it sits now, it does exactly everything else in the query, just like the "!= '$user_id'" wasn't there at all.

I am sure this is a simple matter for all of you pros here, but I am at a loss.

Would you help me please?

Thank you in advance!

Bjom

what do the tables involved look like?

I personally use the mysql administrator tool to test my queries. simply add these lines to the code:

echo $sql;
exit;

then copy/paste the result over to the mysql administrator's query browser. It's much easier to experiment and find errors there.

halojoy

Another way to express:
"WHERE a != b"
that SQL language often support is:
"WHERE a IS NOT b"

But I think this is not your issue.
I would try to remove 'quotes' around $user_id
Very often $id, $user_id are INTEGER .... not strings
So:

r.user_id != $user_id

servant

Thank you for the link to the handy tool. There is a newer version on the site, just for your info.

The problem is that it doesn't throw any errors. It just ignores the portion of code I mentioned before. Totally as if it weren't there at all.

One of my friends said she remembered that you can't use NOT EQUAL, !=, <> or anything like it in a JOIN since it will be ignored. Sounds like my issue, but she didn't recall what to do to make it work, and I have no idea.

The DB table the user ID is being pulled from is INT(8) and I don't know what else you would need to know since the problem is localized to the one portion of the select.

Thank you!

Bjom;10945965 wrote:
what do the tables involved look like?

I personally use the mysql administrator tool to test my queries. simply add these lines to the code:

echo $sql;
exit;

then copy/paste the result over to the mysql administrator's query browser. It's much easier to experiment and find errors there.

servant

Thank you very much for the input, but that is not the issue.

I know a gal who says that any form of NOT EQUAL operation will fail inside of a join. Sounds like my kind of issue, but I don't know how to fix it. Any help in re0writing this quesry so it works?

Thank you!

halojoy;10945967 wrote:
Another way to express:
"WHERE a != b"
that SQL language often support is:
"WHERE a IS NOT b"

But I think this is not your issue.
I would try to remove 'quotes' around $user_id
Very often $id, $user_id are INTEGER .... not strings
So:
r.user_id != $user_id 

Bjom

If you refer to the mysql workbench or workstation or whatever it is called: yes I know. I prefer the good old administrator for the moment - that new thing is a bit huge and not to my taste (at least for the moment)

Not in a join....well you do not use it in a join but in a WHERE. So that should not be the issue. Try to replace the comma join with an ansi join tho - might make the whole thing a bit easier to read ( INNER JOIN .... ON ...)

Make a test case and give us all info. That's usually much better than saying "I've narrowed it down to..." because sometimes a minor detail somewhere else pops up where you do not expect it and causes the very problem that you thougth you had narrowed down.

So what I would suggest is: make tiny tables with test data, share them here and state exactly what you expect and what you get. Like so:

Query: ...

expected result: ...

but I get : ....

hth

Bjom

laserlight

servant wrote:
One of my friends said she remembered that you can't use NOT EQUAL, !=, <> or anything like it in a JOIN since it will be ignored. Sounds like my issue, but she didn't recall what to do to make it work, and I have no idea.

servant wrote:
I know a gal who says that any form of NOT EQUAL operation will fail inside of a join.

It might fail as the join condition because it usually does not make sense to do that: logically, you would end up with a result that is almost as bad as a Cartesian product. However, here it is not involved in the join condition, at least according to what you posted.

I agree with Bjom: post the smallest and simplest example that demonstrates the problem.

igorek

// break ur sql up its ugly ><
// lets call it query much better then stractured query language
$query  = "SELECT SQL_NO_CACHE DISTINCT r.post_id "; 
$query .= "FROM simple_responses r, simple_posts p "; 
$query .= "WHERE r.post_id = p.post_id ";
$query .= "AND p.post_time > '$cuttime' "; 
$query .= "AND p.public = '1' ";
$query .= "AND r.user_id != '$user_id' "; 
$query .= "AND p.post_deleted = '0' ";

when you run ur query in console what u get back?

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!

servant

Thank you very much! This is what I was going through doing when I realized that I had more data returning than I realized.

I appreciate the help very much!

Bjom;10945991 wrote:
So what I would suggest is: make tiny tables with test data, share them here and state exactly what you expect and what you get. Like so:

table r:
ID | userID | bla | anotherField
1 | 3 | adsf | something
2 | 3 | asdf | something else
3 | 1 | lask | ...

table u:
ID | userName | userHasGun
1 | Jamie | yes
2 | Jackie | no
3 | Joline | no

Query: ...

expected result: ...

but I get : ....

hth

Bjom