Get table on the fly with sqlite

Question

Hello,

I've a code that display a list of items based on a custom adapter. To display a content of a table no problem but when I need to add a reference to many table it's another thing. Two solution are possible in my point of view:

1) Querying the database inside setViewValue (didn't investigate too much this possibility) or 2) Find a way to associate dynamically a column to my other tables

(solution for both are welcome)

I have a main table where a column called slug is a reference to other tables. These other tables contain some data which I would like to count.

Once I have my query I use a SimpleCursorAdapter to display a nice list in android (eg 1024 (2 ref), 2048 (3 ref)...).

I think a little schema is probably better than my explanation.

Table: A
-----------------------
_id    | slug | value |
-----------------------
1      | a_a  | 1024  |
2      | g_z  | 2048  |
-----------------------


Table: mytable_a_a
----------------
_id    | value |
----------------
1      | xxx   |
2      | yyy   |
----------------

Table: mytable_g_z
----------------
_id    | value |
----------------
1      | xxx   |
2      | yyy   |
3      | zzz   |
----------------


The goal is to produce something like

-------------------------------
_id    | slug | value | count
-------------------------------
1      | a_a  | 1024  | 2
2      | g_z  | 2048  | 3
...

I've tried something like:

SELECT * FROM A UNION SELECT COUNT(*) FROM A.slug;

but I'm missing the point and if a solution exists it would be a bit more complicated.

possible workaround: include a counter in the table a

Thank you

Answer 1

Since your table names can only be obtained from a query, you need to use dynamic SQL, i.e. concatenate the SQL query string based on values that you previously obtained from the database. This is a little clumsy at best. You can make this much easier by designing it differently, for instance by storing all your mytable_x_x data in a single table and using x_x as a key that you can join on. Then you can get the result in a single query.