GiantKiwi GiantKiwi - 1 year ago 105
MySQL Question

MySQL - Select where both field1 and field2 are greater than 0

I'm trying to do a select query which I select from a table where field1 and field2 are both greater than 0.

NB field1 & field2 are both INT / NOT NULL.

i've tried doing something along the lines of:

SELECT * FROM tblname WHERE field1 > "0" AND field2 > "0"

SELECT * FROM tblname WHERE field1 <> "0" AND field2 <> "0"

SELECT * FROM tblname WHERE field1 != "0" AND field2 != "0"

However, when i was just matching whether either or was = 0, this query does work -

SELECT * FROM tblname WHERE field1 = "0"

I need to check these to produce a list of applicants who've received a grading on their applications, so both field1 and field2 must have been satisfied, so it is assumed that with numerical grading that matching for greater than 0 would work, but for some reason it doesn't?

Answer Source

For a neater code, you can use LEAST() which takes the smaller argument placed inside the parantheses .

I believe your problem is that you are comparing ranges on strings instead of numbers( you mentioned those are numeric fields) :

WHERE LEAST(field1,field2) > 0

If that doesn't solve your problem - edit your question and define "doesn't work" , nothing is being selected? Throws an error? ..

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download