SQL query does not want to work

I know this SQL query is wrong, or I am missing something, but I have a table full of messages. I want get all the messages where the user id is ether 1 or 2, and the reply_to is 0. But with my SQL 开发者_如何学Cquery, it shows the replies, and does not now the posts from user 2.

Here is my SQL

SELECT *
FROM `msg`
WHERE
  `userid` LIKE '1'
  OR
  `user_id` LIKE '2' AND `reply_to` LIKE '0'
ORDER BY `timestamp` DESC
LIMIT 0,10; 

How would I fix my query to accomplish what I want?

how about this?:

SELECT * FROM `msg` 
    WHERE `userid` in ('1','2') AND `reply_to`='0' 
    ORDER BY `timestamp` DESC LIMIT 0,10; 

You have a condition that says:

user=1 or user=2 and reply_to=0

If this is evaluated like (user=1 or user=2) and reply_to=0 then you would get the result that you want, non-replied messages where the user is either user1 or user2. However, if it is evaluated like user=1 or (user=2 and reply_to=0) will return all user1's messages, and non-replied messages for user2, so there is some ambiguity.

My advice is: Never, and I mean never, mix an and and an or in the same condition. There are, of course, precedence rules, that will tell you that p or q and r is evaluated one way or the other, but they can be different in different platforms, in different languages, maybe even in different implementations. And imagine if instead of 3 there were 15 conditions?

Always use parenthesis to separate and/or blocks. The compiler or the database do not care or need them, but out puny human minds work better when we can envision what the computer is actually instructed to do.

You could rewrite your query like:

SELECT * FROM msg
  WHERE (userid=1 OR user_id=2)
    AND reply_to = 0
ORDER BY timestamp DESC

where the relation between the conditions is more obvious.

P.S. Avoid using LIKE if you are not actually using the LIKE wildcards.

TRY

SELECT * FROM `msg` 
        WHERE `userid` IN (1,2) AND `reply_to`='0' 
        ORDER BY `timestamp` 
        DESC LIMIT 0,10; 

Looks to me as if you need to add brackets to your WHERE clause to ensure that the execution sequence of the OR and AND parts is the way you want it.

Try:

SELECT * 
FROM `msg` 
WHERE (`userid` = '1' OR `user_id` = '2') 
AND `reply_to` = '0' 
ORDER BY `timestamp` DESC LIMIT 0,10;

The brackets are the important bit. You should use = rather than Like when you're not searching for wildcards.

You could also use in for the user_id check.

You need to use parenthesis (I'm assuming these work in mysql like they do in mssql) to help it know what conditions of the where clause are acceptable.

SELECT *FROM `msg`
WHERE  (`userid` LIKE '1'  
OR  `user_id` LIKE '2') 
AND `reply_to` LIKE '0'
ORDER BY `timestamp` 
DESCLIMIT 0,10; 

Depending on what you're actually after in your conditional, you either want:

SELECT * FROM `msg` 
WHERE (`userid` IN ('1','2'))
  AND (`reply_to` = '0')
ORDER BY `timestamp` DESC
LIMIT 0, 10;

...or:

SELECT * FROM `msg` 
WHERE (`userid` = '1')
   OR (
         (`userid` = '2') 
     AND (`reply_to` = '0')
   )
ORDER BY `timestamp` DESC
LIMIT 0, 10;