Join with sqlite on table in android

sqlite

Given a table like:

object    subject    ID      parentID
------    -------   -----    --------
  A         NULL      1          0
  B         NULL      2          0
  C         NULL      3          0
  D         NULL      4          0
  E         NULL      5          0
 Null        CC       6          1
 Null        DD       7          2
 Null        EE       8          2

I need a query with good performance that returns the following result:

result
------
  A
  CC
  B
  DD
  EE

Best Answer

You can use coalesce, it's a function that returns its leftmost non-null argument:

select coalesce(object, subject) 
from t;

Coalesce is a shorter form of:

select case when object is not null then object else subject end
from t;

Related Solutions

Ny way to get the position of a value in a table returned by a SELECT statement

There is a ROW_NUMBER() analytic function but I don't think it's available in SQLite. So, here's a generic approach (which may be quite slow):

SELECT pos - 1 AS ROWNUMBER     --- the "-1" is to get the C-style row numbering
FROM
  ( SELECT b.id
         , COUNT(*) AS pos 
    FROM myTable AS a
      JOIN myTable AS b
        ON a.id <= b.id 
    WHERE a.aCondition = 1
      AND b.aCondition = 1
      AND a.id <= 4             --- optional for  
      AND b.id <= 4             --- performace 
    GROUP BY b.id
  ) AS tmp
WHERE id = 4

Note that you should not use the additional two lines (checks), if your final WHERE checks for something else than id.

Rethinking, there's an even better way, because you only want the Row_Number of the last element in that list. There is no need to number them all, we can just count them:

SELECT CASE WHEN maxid = 4     --- so we don't get a RowNumber for an @id
              THEN cnt-1       --- that does not meet the condition
              ELSE NULL        --- or is not in the table at all 
       END AS ROWNUMBER    
FROM
  ( SELECT MAX(id)  AS maxid
         , COUNT(*) AS cnt 
    FROM myTable AS a
    WHERE a.aCondition = 1
      AND a.id <= 4   
  ) AS tmp

SQLite/Android – First Time Using an Index

WHERE c = ?

This lookup can be optimized with a simple index on the c column:
```
CREATE INDEX T_c on T(c);
```
WHERE a LIKE ? || '%'

There are several restrictions on indexes that can be used for LIKE searches. In this case, you need a case-insensitive index:
```
CREATE INDEX T_a_ci ON T(a COLLATE NOCASE);
```

WHERE b LIKE ? || '%'

same here:

CREATE INDEX T_b_ci ON T(b COLLATE NOCASE);

WHERE a LIKE ? || '%' AND b LIKE ? || '%'

The database can use at most one index per table in a query, so it will use one of the indexes above. If you have run ANALYZE, it might be able to choose the better one.

To check which indexes are actually being used, use EXPLAIN QUERY PLAN.

Whenever you change tables, any indexes must be updated too. When you're filling an empty table, it is usually a better idea to create indexes afterwards.

When your application does many updates to some table, it might be worthwhile omitting some index(es) that are useful only for seldomly-used queries.

Best Answer

Related Solutions

Ny way to get the position of a value in a table returned by a SELECT statement

SQLite/Android – First Time Using an Index

Related Question