mySQL query Where issue

Question

Could somebody tell me what am I doing wrong here?

$query = "SELECT * 
            FROM routes 
           WHERE econ <> maxecon 
        ORDER BY `id`;";

Without the WHERE, it works fine so I know that's the problem.

I want to select data where the column econ is NOT equal to maxecon. Is this even possible at this stage or do I have to take it all and chop it out in a loop?

Answer 1

should 'id' be in single quotes as it is?

Answer 2

Your query should be:

$query = "SELECT * FROM routes WHERE econ != maxecon ORDER BY `id`";

Of course, if econ is equal to maxecon then you will get no results, maybe you could post some sample data from the table?

Answer 3

I'm not a MySql guy (SQL Server instead), but would this help?

$query = "SELECT * FROM [routes] WHERE ([econ] <> [maxecon]) ORDER BY [id];";

or this variation?

$query = "SELECT * FROM [routes] WHERE ([econ] != [maxecon]) ORDER BY [id];";

Answer 4

Does replacing <> with != work?

$query = "SELECT * FROM routes WHERE econ != maxecon ORDER BY `id`";

Also, you don't need to include the ending semi-colon in your sql statement.

Answer 5

I would try != instead of <>.

Answer 6

What you've posted looks ok. Have you definitely got examples where the two values are different? ( look at the results of your query without the WHERE ).

Also, are the fields nullable? Been a long time since I've used MySQL, but on SQL Server, items are not comparable with the <> operator if one of the operands is NULL.

Try :-

SELECT * FROM routes 
WHERE 
   ( econ <> maxecon ) 
OR ( econ IS NULL AND maxecon IS NOT NULL )
OR ( econ IS NOT NULL AND maxecon IS NULL )
ORDER BY id;

Quoted for your implementation language, of course.

Answer 7

Possible problems:

• You may possibly need to backquote the names econ and maxecon.
• You may not have any data that meets the critieria.
• You may have data that meets the critieria, but only in cases where one column is NULL, and these won't be found by <>.

If the your problem is the last one, try NOT <=>, which is the negation of a special "NULL safe equals" operator in MySQL.

Answer 8

What programming language is this? Most I've used don't make you put a semicolon in the query itself...