CODEWITHSUNDEEP
sqlsqliteselectandroid-roomisnull
m5seppal
m5seppal

Reputation: 1226

SQL: how to SELECT by value if value can be null

I have a database table "Category" which can have a self-reference. If self-reference is null, then it's a top-level category (no parent). If it's pointing to another category, it's a subcategory of the given category.

Now I'm trying to setup query which returns all subcategories with given parent category id. Something like following (Android Room syntax):

"SELECT * FROM category WHERE parent_id = :parentId"

This query returns nothing if parentId == null, but I would like it to return all categories where parentId == null (i.e. top-level categories). With a query

"SELECT * FROM category WHERE parent_id IS NULL"

I can get parent categories successfully, but obviously, it's not working for subcategories.

I tried also the following query

"SELECT * FROM category WHERE (parent_id IS NULL OR parent_id = :parentId)

but it fails of course, since it always returns top-level categories, even though the intention is to get only certain set of subcategories.

My question is, how I can set up my query so that it works as expected, i.e. returns all top-level categories when parentId == null, otherwise it returns all subcategories of given category id.

Upvotes: 0

Views: 1382

Answers (2)

CL.
CL.

Reputation: 180162

The SQL standard requires this handling of NULL values.

But SQLite has an extension: you can use IS also with non-NULL values:

SELECT * FROM category WHERE parent_id IS :parentId

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I think you just need a slightly more complicated condition:

SELECT c.*
FROM category c
WHERE (c.parent_id IS NULL AND :parentId IS NULL) OR
      (parent_id = :parentId);

Upvotes: 0

Related Questions