lundi 29 juin 2015

MySQL subtract two columns and compare them to number in WHERE clause

I have a table which looks like this:

id | some | irrelevant | data | time | waste_time | logged_in
--------------------------------------------------------------
1  |      |            |      | 0    | 246        | 0
2  |      |            |      | 0    | 360        | 0
3  |      |            |      | 116  | 116        | 0
4  |      |            |      | 291  | 199        | 0
5  |      |            |      | 3997 | 520        | 0
6  |      |            |      | 0    | 175        | 0
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

I am trying to have multiple tables, based on the result of time-waste_time I tought I had it work, but I was wrong. If the result is negative number, the compare to 0 (time-waste_time > 0) just gets ignored.

Those are my queries:

SELECT * FROM players WHERE time-waste_time>0 OR logged_in=1 ORDER BY logged_in DESC, login DESC

SELECT * FROM players WHERE time-waste_time>0 AND login<$time_limit ORDER BY login DESC

SELECT * FROM players WHERE time-waste_time<=0 AND logged_in=0 ORDER BY login DESC

The first query will show player that has time 0 and waste_time 150, even tho 0-150 = -150 and -150 < 0 and the third query will not show such player. Re required result is, that such player will not be shown with first and second query, but will be shown with third one.

I already found this anwer: Using 'Greater than' operator with a negative number but its not helping.

Any ideas why its acting like this and how can I make it work?

Ps.: logged_in IS ZERO

Thanks.

Aucun commentaire :

Enregistrer un commentaire