How do I use a boolean field in a where clause in SQLite?

Question

It seems like a dumb question, and yet. It could be my IDE that's goofing me up. Here's the code (this is generated from DbLinq):

SELECT  pics$.Caption, pics$.Id, pics$.Path, pics$.Public, pics$.Active, portpics$.PortfolioID
FROM main.Pictures pics$
inner join main.PortfolioPictures portpics$    on  pics$.Id = portpics$.PictureId

WHERE   portpics$.PortfolioId = 1 AND pics$.Id > 0
--AND pics$.Active = 1 AND pics$.Public = 1
ORDER BY pics$.Id

If I run this query I get three rows back, with two boolean fields called Active and Public. Adding in the commented out line returns no rows. Changing the line to any of the following:

pics$.Active = 'TRUE'
pics$.Active = 't' 
pics$.Active =  boolean(1)

It doesn't work. Either errors or no results. I've googled for this and found a dearth of actual SQL queries out there. And here we are.

So: how do I use a boolean field in a where clause in SQLite?

IDE is SQLite Administrator.

Update: Well, I found the answer. SQLite Administrator will let you make up your own types apparently; the create SQL that gets generated looks like this:

CREATE TABLE [Pictures] ([Id] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[Path] VARCHAR(50)  UNIQUE NOT NULL,[Caption] varchAR(50)  NULL,
[Public] BOOLEAN DEFAULT '0' NOT NULL,[Active] BOOLEAN DEFAULT '1' NOT NULL)

The fix for the query is

AND pics$.Active = 'Y' AND pics$.Public = 'Y'

The real issue here is, as the first answerer pointed out, there is no boolean type in SQLite. Not an issue, but something to be aware of. I'm using DbLinq to generate my data layer; maybe it shouldn't allow mapping of types that SQLite doesn't support. Or it should map all types that aren't native to SQLite to a string type.

Synopsis

Answer 1

SQLite does not have the boolean type: What datatypes does SQLite support?

The commented-out line as it is should work, just use integer values of 1 and 0 in your data to represent a boolean.

Melodize

Must be SQLite Administrator that's goofing me up. It has a boolean type available when you build the table. But querying on that field always gives no results. – Synopsis

see my answer in #4825187 – Pronoun

Answer 2

You don't need to use any comparison operator in order to compare a boolean value in your where clause.

If your 'boolean' column is named is_selectable, your where clause would simply be: WHERE is_selectable

Slue

What's the converse of this? If you were trying to find those that were not selectable. – Darya

WHERE NOT(is_selectable) – Slue

Good, but what if I want the NULL value to be considered as a boolean false? – Sallee

WHERE COALESCE(is_selectable_maybe_null, false) sqlite.org/lang_corefunc.html#coalesce – Slue

Answer 3

SQLite has no built-in boolean type - you have to use an integer instead. Also, when you're comparing the value to 'TRUE' and 't', you're comparing it to those values as strings, not as booleans or integers, and therefore the comparison will always fail.

Source: http://www.sqlite.org/datatype3.html

Cardew

Answer 4

--> This Will Give You Result having False Value of is_online field

select * from device_master where is_online!=1

--> This Will Give You Result having True Value of is_online field

select * from device_master where is_online=1

Socket