MySQL exclusion <> only working when not accompanied with OR when comparing other columns

Question

SQL Fiddle here: http://sqlfiddle.com/#!2/a2e41/8

I have a query which does a number of checks: whether column uidto or uidfrom contain a given value. This works fine, but as soon as I want to exclude when columns hidden1 or hidden2 contain a given value the query returns the results anyway. As soon as I take out the first uidto, uidfrom check, it returns nothing, which is expected. Is there any way to do all the checks? Is one being ignored in favor of the other?

Any ideas?

edit:

This still returns despite column hidden1 containing the value to check for:

SELECT m.threadid,
m.uidto,
m.uidfrom,
m.type,
m.hidden1,
m.hidden2
FROM messages m
WHERE m.uidto = 1
OR m.uidfrom = 1
AND m.hidden1 <> 1
AND m.hidden2 <> 1
GROUP BY threadid;

This seems to honor the exclusions, but does not do the check for uidfrom or uidto containing 1

SELECT m.threadid,
m.uidto,
m.uidfrom,
m.type,
m.hidden1,
m.hidden2
FROM messages m
WHERE m.hidden1 <> 1
AND m.hidden2 <> 1
GROUP BY threadid;

EDIT:

There are several threadid's and I pull the messages belonging to each user involved in the thread (there will only ever be two users) by checking if a value matches uidto or uidfrom, then checking if a users id is in hidden1 or hidden2.

the value of the hidden1 and hidden2 columns is the users id who has opted to hide the thread from himself.

If user 1 deletes his thread, we put his user id (1) into the hidden1 column so that he can't see it, but the other user can. if user id 22, who is involved in the thread as well wants to delete the thread, user id 22 would go in to hidden2, now neither of them can see that thread.

Message threads will only ever be seen by the person who sent the messages in it, or the person receiving them. Nobody else will be involved.

omg... it's so confusing.... lol... sorry. I am trying. I don't care about the "business rules" of if a user deletes his thread or what not. I need just the conditional logic of the query you want to run. So... if... if you put it into the hidden column, is it still in the corresponding userid column? Are you able to write it as... if `user_id_to is null` then i need to check if `hidden = 1`... or something? — gloomy.penguin, Nov 14 '13 at 03:20
I think you want to split the conditions (maybe?). So... `where (m.uidto = 1 and hidden1 <> 1) or (m.uidfrom = 1 and hidden2 <> 1)` or something like that. (it just depends on me understanding your logic at this point) Don't worry about `null` right now, if we can get the rest of the query exactly how you want, they're easy to account for. — gloomy.penguin, Nov 14 '13 at 03:23
is the query trying to find....? all conversations where both users are hidden? one user is hidden? what? — gloomy.penguin, Nov 14 '13 at 03:25
@user623952 i got it sorted, but thanks for your willingness to help. basically i just need the column default to not be NULL. using default 0 worked. — , Nov 14 '13 at 03:59
okay.... sorry I couldn't be of more help. People have problems with `null` a lot. I personally like them, though. — gloomy.penguin, Nov 14 '13 at 04:06

score 0 · Accepted Answer · edited May 23 '17 at 12:05

0

Try this query and let me know.

SELECT   m.threadid,
         m.uidto,
         m.uidfrom,
         m.type,
         m.hidden1,
         m.hidden2

FROM     messages m

WHERE    (m.uidto = 1 OR m.uidfrom = 1)
         AND m.hidden1 <> 1 AND m.hidden2 <> 1

GROUP    BY threadid;

the parentheses matter... without them, it assumes you meant this:

WHERE    m.uidto = 1 OR ( m.uidfrom = 1  AND m.hidden1 <> 1 AND m.hidden2 <> 1 )

And... it's determined by order of precedence for the operators! I was going to update my answer to include info on that but then I found this amazing SO Post that covers it very well: Mysql or/and precedence?

for clarification: http://sqlfiddle.com/#!2/5f989/26/0

select  ThreadID, 

        Type, 

        UIDto, 
        Hidden1, 

        case when UIDTo is null     and Hidden1 is not null then concat('UserTo (',Hidden1,') is hidden')
             when UIDTo is not null and Hidden1 is null     then concat('UserTo (',UIDTo,') is NOT hidden')
             else  'UserTo ('+UIDto+') status is unknown' end as UIDTo_Status,

        UIDfrom, 
        Hidden2,

        case when UIDFrom is null     and Hidden2 is not null then concat('UserTo (',Hidden2,') is hidden')
             when UIDFrom is not null and Hidden2 is null     then concat('UserTo (',UIDFrom,') is NOT Hidden')
             else  'UserTo ('+UIDfrom+') status is unknown' end as UIDfrom_Status


from    messages

edited May 23 '17 at 12:05

Community

1
1

answered Nov 14 '13 at 00:02

gloomy.penguin

5,833
6
33
59

That seems to be giving me the expected result. Must all OR operators be surrounded in parenthesis or is it just when you are going to be using other operators as well? (understood, thanks) – Nov 14 '13 at 00:06
it depends... when you mix `OR` and `AND`, you should always use parentheses so you can be sure you're getting the correct results back. – gloomy.penguin Nov 14 '13 at 00:07
2

@cjaredrun it is about [operator precedence](http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html). `AND` goes before `OR` – Gustek Nov 14 '13 at 00:08
2

Operator precedence in arithmetic may be more familiar. For example: `10+10*10 = 110`, whereas `(10+10)*10 = 200`. – Bill Karwin Nov 14 '13 at 00:09
@user623952, every database and every programming language I've ever used does Boolean operator precedence the same. http://en.wikipedia.org/wiki/Logical_connective#Order_of_precedence – Bill Karwin Nov 14 '13 at 00:12
I think `and` is always before `or` in any programming language, database and others that have logic operators but it is always better to check just to be sure ;] – Gustek Nov 14 '13 at 00:12
oh, well... that's an exciting link. I love logic. – gloomy.penguin Nov 14 '13 at 00:15
@user623952 et, all. after playing with the query and adding other message threads, some marked hidden some not, the query seems to be blanketing them all because one of the threads has a hidden1 filled with a value, thus ignoring every other thread associated to that user. I've been playing here http://sqlfiddle.com/#!2/e3c39c/2 So even I've only filled hidden1 on the threadid 1, why would the query assume I don't want any of the other threads? – Nov 14 '13 at 01:46
@cjaredrun - wait, what...? pay attention to using the `or` in the second condition, is that what you want? can you update the post up top with some sample data and what you expect as output? just write the conditions out... don't do it in code. just say what you want. don't talk about what it's doing or not doing. and `null` is a state of undefined... it's not a value, so if you need to include it somehow, you have to do `(value = 1 or value is null)`. (i'm still not clear on what you want exactly) – gloomy.penguin Nov 14 '13 at 02:42
@user623952 i _may_ have fixed it by making the database hidden1 and hidden2 column defaults 0 instead of NULL. Will keep testing that, but it appears to be doing what I want. – Nov 14 '13 at 03:10

MySQL exclusion <> only working when not accompanied with OR when comparing other columns

1 Answers1