need a strategy for a tough sqlite query in Android

I will simplify as much as possible.

Table A, an event table, has 4 columns: _id, timestamp, type, source_id
Table B, a source table for a type: _id, a number of other columns unique to B
Table C, a source table for a type: _id, a number of other columns unique to C
Table D, a source table for a type: _id, a 开发者_StackOverflow中文版number of other columns unique to D

I am storing up to 200 of these events (for now, may go into the 1000s), they will be of type B,C,D. When loading these events, I need to create event objects that look something like this

Event {
    id
    timestamp
    type
    source object (can be an object of type B,C,or D, which share a base class)
}

My only current strategy:

• pull all event objects
• do 3 seperate queries (Select * from B where id in (compiled_list_of_ids_of_type_B), same for c, same for d)
• Store results from above 3 queries in 3 different HashMaps so they can be easily accessed by id
• iterate through the event objects and store the source objects in their proper events

I am not crazy about this, the querying strategy seems pretty inefficient. Any ideas on how to do this more efficiently?

Thanks

---

You can reduce to 3 queries:

select from A where type = "type that leads to B" join on B with whatever fields you need

same for C

same for D

and while you read the results construct one complete event object for each line and insert them in whatever container you hold the final list in.
It may save a bit of memory too, considering you don't store any temporaries this way. Not much of it though.

---

add an association table.

a a2other b c d

query a and a2other

a2other will tell you what table and what id.

then you can sort the a2other result and query the b c and d singularly.