How to create a sql view (CREATE VIEW) in sqlite(android) and query?

So I have a table and I want to create another table using “CREATE VIEW” from sql. I need to make a copy of the table that I am working with so I can use it 2x. My sql query would have to be like this:

SELECT A.time AS Start, B.time AS Stop
FROM time A, time B
WHERE A.id+1=B.id
AND A.bool=1
AND B.bool=0

my initial table is:

    String sql="create table "+TABLE+" ( "+C_ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "
+C_TIME+" TEXT, "+C_BOOL+" NUMERIC)";

so anyone has any ideea where (in my code) I can create the view and how do I query it in android?

I can provide code if needed

Thank you 🙂

Answer

Based on this, you would create the view with the following statement:

CREATE VIEW view_name AS 
    SELECT A.time AS Start, B.time AS Stop
    FROM time A, time B
    WHERE A.id+1=B.id
        AND A.bool=1
        AND B.bool=0

You can create it right after you create the “base” table.

You can query it just like you would query any other table.