Querying from multiple tables in Android SQLite?

Question

I was wondering if it's possible (it should be) to query multiple tables simultaneously (several at once) in SQLite. Basically I have several tables that have the exact same columns, but the data in them is just organized by the table it's in. I need to be able to use SELECT to get data from the tables (I heard UNION could help), which matches a condition, then group the data by the table it's in.

In other words, would something like this be possible?

SELECT * FROM table1,table2,table3,table4,table5,table6 WHERE day=15 GROUP BY {table}

I'd rather not resort to having to query the tables individually as then I would have a bunch of Cursors that I'd have to manually go through and that would be difficult when I only have one SimpleCursorAdapter? Unless a SimpleCursorAdapter can have several Cursors?

Thanks.

EDIT: The structure of my tables:

Main Table - contains references to subtables in a column "tbls"
             and meta-information about the data stored in the subtables

    Subtable - contains reference to subsubtables in a column "tbls"
               and meta-information about the data stored in the
               subsubtables

        Subsubtable - contains the actual entries

Basically these tables just make it easier to organize the hierarchical data structure. I suppose instead of having the subsubtables, I could keep the actual entries in the subtable but add a prefix, and have a separate table for the meta-information. It just seems it would be harder to delete/update the structure if I need to remove a level in this data set.

Can you elaborate on the reason for the many tables, instead of storing the data in a single table with perhaps a column added to differentiate the sets? This multi-table approach seems unusual at first glance. — Tim
– Tim, Commented Jul 12, 2011 at 19:54
I was thinking the same thing, if they're the same type of data, but differentiate by table, it seems like you're missing the point of columns — Samuel
– Samuel, Commented Jul 12, 2011 at 20:10

Ashkan · Accepted Answer · 2011-07-12 20:42:38Z

2

You can create view based on your tables, the query of your view is union of your tables.

create view test as select * from table1 union select * from table2

now you can filter data as you want for more info check union & view

http://www.w3schools.com/sql/sql_union.asp

http://www.w3schools.com/sql/sql_view.asp

answered Jul 12, 2011 at 20:42

Ashkan

3202 silver badges17 bronze badges

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

Comments

Oleg Vaskevich · Accepted Answer · 2011-07-13 04:11:45Z

0

In the end, I decided to forgo having many subsubtables, and instead adding another column like Tim and Samuel suggested. It will probably be more efficient as well then chaining SELECTs with UNION.

answered Jul 13, 2011 at 4:11

Oleg Vaskevich

12.8k6 gold badges65 silver badges81 bronze badges

Collectives™ on Stack Overflow

Querying from multiple tables in Android SQLite?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related