Selecting the items that don't match - SQLite

[quailcreek]

Seems like this should work but alas... no-joy. I'm trying to select all of theNames where theType does not match the string. It seems that SQLite doesn't like the <> because it ignores it. What is the syntax to accomplish this?

Code: Select all

  put "SELECT theName FROM MyNames WHERE theType <> 'Production' OR theType <> 'Device'" into tSQLStatement

[Simon]

Hi quailcreek,
Did you try "!="

Simon

[quailcreek]

Yep, I tried that Simon. It gets ignored too. Seems like it should throw an error but it doesn't

[SparkOut]

Are you sure the <> is being ignored?
It looks like your query will return all results because all of the records will be either not equal to production or not equal to device. (Everything)
Try changing OR to AND in the query.

[quailcreek]

Hi Sparkout,
That did it. Changed OR to AND and now both <> and != work. Seems somehow counter intuitive. I would have thought that OR meant the string could contain either Production or Device and AND meant the string needs to contain both. I guess the way SQLite does the boolean is exclude (Production AND Device). Hmm... looks like I explained it to myself.

Thanks, Sparkout.

[SparkOut]

This is actually normal boolean resolution. It might look counterintuitive at first glance but really it is quite logical.
What you were asking the query to match is where the first condition was true (theType <> 'Production') OR the second condition was true (theType <> 'Device') . If theType is Production then the first condition is false so the second condition will be checked. If theType is Production then it obviously is not Device so the overall query will resolve true and return the record, and vice versa for theType being Device. Thus every record would be returned.

[quailcreek]

Thanks, Sparkout. It works a little differently then I thought but it makes sense.