Database query with relationships in Android

Question

suppose I have the following tables

sqlite> SELECT * FROM Artists;
ArtistID|ArtistName
1       |Peter Gabriel
2       |Bruce Hornsby
3       |Lyle Lovett
4       |Beach Boys
5       |Supernatural

sqlite> SELECT * FROM CDs;
CDID|ArtistID|Title              |Date
1   |1       |So                 |1984
2   |1       |Us                 |1992
3   |2       |The Way It Is      |1986
4   |2       |Scenes from the Southside|1990
5   |1       |Security           |1990
6   |3       |Joshua Judges Ruth |1992
7   |4       |Pet Sounds         |1966

I want to make this kind of query

sqlite>SELECT ArtistID as _id, ArtistName, Title FROM Artists, CDs WHERE Artists.ArtistID=CDs.ArtistID;  

How can I use that using the Android SQLiteQueryBuilder class, what do I need to put for my projectionMap?

Or would it be easier just using the SQLiteDataBase class?

Answer 1

How can I use that using the Android SQLiteQueryBuilder class

Well, I'd just use rawQuery() on SQLiteDatabase for that, since you know SQL and have the SQL.

If somebody held a gun to your head to force you to use SQLiteQueryBuilder, your projection map is probably mapping ArtistName to ArtistName or Artists.ArtistName and Title to Title or CDs.Title. I have not used SQLiteQueryBuilder with an implicit (or explicit) JOIN, in part because I only use SQLiteQueryBuilder when I write a content provider.

Answer 2

Ok I figured it out. Still had to hardcode the where statement.

projectionMap.put(ArtistID, ArtistID + " AS " + _ID);
projectionMap.put(Title, Title);
queryBuilder.setProjectionMap(projectionMap);