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

Question

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 :)

user610650 · Accepted Answer · 2012-04-20 09:35:29Z

19

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.

answered Apr 20, 2012 at 9:35

user610650

Sign up to request clarification or add additional context in comments.

5 Comments

Diana C Over a year ago

I know it sounds stupid but I have a question: does sql know that A.time and B.time are rows? if not, how do I do that. (sorry, but i am pretty new with sql)

user610650 Over a year ago

sqlite knows that A.Time and B.time are columns. Just to be clear, your time table has a column time?

Diana C Over a year ago

my 'time' table has a column named 'times'

user610650 Over a year ago

a column named times or time?

Diana C Over a year ago

i think I need to change the name... it is confusing. the column is with s

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related