CODEWITHSUNDEEP
mysqlsql
Vitaly Lebedev
Vitaly Lebedev

Reputation: 131

SQL query with condition when one field not equal to another

Let's say we have fields: id | content | updateTime | creationTime

When insertion done both updateTime and creationTime get filled with current time. On update only updateTime field get changed. When I need to find all data created on '2012-06-28' I simply use: 

SELECT id, content FROM tbl WHERE creationTime LIKE '2012-06-28%'`

But to get all data that was updated excluding created data I need to use something like this:

SELECT id, content FROM tbl WHERE creationTime LIKE '2012-06-28%' AND creationTime != updateTime

This can't work, obviously. Though I could find all updated data by comparing values of two 'time' collumns inside php, I'd still love to do it inside query.

UPD: Well, it turns out that I was absolutely unaware that sql allows not only compare value of a field with a given variable, number... but also it can compare value of one field with another.

Upvotes: 4

Views: 57865

Answers (3)

bluehallu
bluehallu

Reputation: 10295

Your query is correct (on mySQL¹), don't know why would you think otherwise

¹ <> is the SQL operator for not equal. Some implementations of it (PostgreSQL or MySQL for instance), however, accept != too. It is a good practice to always use <> to avoid problems if you ever use your code on a different database.

Upvotes: 3

DonCallisto
DonCallisto

Reputation: 29932

Use <> instead != (that isn't compatible with some DMBS)

So 

SELECT id, content 
FROM tbl 
WHERE creationTime LIKE '2012-06-28%' AND creationTime <> updateTime

If you want some documentation you can find here

However, your query have to work properly with !=

Upvotes: 9

Shaikh Farooque
Shaikh Farooque

Reputation: 2640

Only change the not operator from != to <>

SELECT id, content FROM tbl WHERE creationTime LIKE '2012-06-28%' AND creationTime <> updateTime

Upvotes: 1

Related Questions