Trouble with Cursor from rawQuery in SimpleCursorTreeAdapter: too many tables?

Question

I am having some trouble in using a rawQuery to get a cursor for use in an ExpandableListView. I have an adapter class called ExpandableDataAdapter which extends SimpleCursorTreeAdapter. Here is the line I have to create an instance of the ExpandableDataAdapter:

ExpandableDataAdapter confDataAdapter = new ExpandableDataAdapter(
       this,
       groupCursor,
       R.layout.detaillist_group,
       new String[]{ "TrackName" },
       new int[]{ R.id.rowText },
       R.layout.detaillist_child,
       new String[]{ "Title", "Speakers", "TrackName", "Room" },
       new int[]{ R.id.sessionTitle, R.id.sessionSpeakers, R.id.sessionTrack, R.id.sessionRoom }
      );

Inside the ExpandableDataAdapter class definition, I have the following method to get the children cursor for a group cursor:

protected Cursor getChildrenCursor(Cursor groupCursor) 
{
     // Given a group, return a cursor for all the children in that group.
     Cursor talksCursor = _db.rawQuery("SELECT t1.TalkID as _id, t1.Title," +
     " 'None' as Speakers, t2.TrackName, t1.Room" +
     " from talks t1, tracks t2" +
     " where t1.TrackID = t2.TrackID" +
     " and t2.TrackID=?", new String[]{ groupCursor.getString(groupCursor.getColumnIndex("_id")) });   
   startManagingCursor(talksCursor);
   return talksCursor;
}

The above query works, and the ExpandableListView shows up. But, of course, the above is a simpler version of the actual query which I want to execute. When I replace it with the actual (and somewhat more complicated) query, I get an exception. First, the query:

protected Cursor getChildrenCursor(Cursor groupCursor) 
{
   // Given a group, return a cursor for all the children in that group.
   Cursor talksCursor = _db.rawQuery("SELECT t1.TalkID as _id, t1.Title," +
     " group_concat(t3.Speaker, ', ') as Speakers, t2.TrackName, t1.Room" +
     " from talks t1, tracks t2, talkspeaker t3" +
     " where t1.TalkID = t3.TalkID" +
     " and t1.TrackID = t2.TrackID" +
     " and t2.TrackID=?" +
     " group by _id, t1.Title, t2.TrackName, t1.Room", 
      new String[]{ groupCursor.getString(groupCursor.getColumnIndex("_id")) });
   startManagingCursor(talksCursor);
   return talksCursor;
}

The exception I get is: column 'Room' does not exist. This latest query works fine from a database prompt, so I am guessing that something is happening when being executed by android engine. Also, the exception statement has me stumped as well. Am I using too many tables? Is the group_concat function creating trouble? Any pointers?

More notes: the exception is thrown from the constructor of the adapter, which is defined as follows. I am calling the constructor as shown in the first snippet in this post.

public ExpandableDataAdapter(Context context, Cursor cursor,
      int groupLayout, String[] groupFrom, int[] groupTo,
      int childLayout, String[] childFrom, int[] childTo) 
 {
       super(context, cursor, groupLayout, groupFrom, groupTo, 
                             childLayout, childFrom, childTo);
 }

Answer 1

It's a problem with your SQL. You can only select the fields that are in your Group By clause. All other fields in the SELECT clause have to be an aggregate. So you probably have to add t2.TrackName and t1.Room to your Group By clause if that is even possible with your data set.

Answer 2

Figured it out - looks like I had to explicitly declare the column names in the second query. Frankly, I am not sure if this was indeed the problem or if this is the correct solution, but at list, the ExpandableListView is showing up. The working snippet is:

Cursor talksCursor = _db.rawQuery("SELECT t1.TalkID as _id, t1.Title as Title," +
        " group_concat(t3.Speaker, ', ') as Speakers, t2.TrackName as TrackName, t1.Room as Room" +
    " from talks t1, tracks t2, talkspeaker t3" +
    " where t1.TalkID = t3.TalkID" +
    " and t1.TrackID = t2.TrackID" +
    " and t2.TrackID=?" +
    " group by _id, t1.Title, t2.TrackName, t1.Room", 
        new String[]{ groupCursor.getString(groupCursor.getColumnIndex("_id")) });