0

I am struggling to get more complicated queries to work with SimpleCursorAdapter and ViewBinder with a ListView. When I was just returning all entries in my table, that was no problem. However, I want to return a list of artist names from my tables in order of name. The big problem concerns the "rowid _id" field which SimplerCursorAdapter/ViewBinder expects.

My code worked fine when I had queries of the form SELECT rowid _id, Artist.NAME etc, but I want to use the DISTINCT keyword to return the unique set if artist names. I can't put "rowid _id" before "DISTINCT Artist.Name" and I can't put it after. What is the solution for this?

The query I want (A) is (shown without the "rowid _id"):

String sQuery = String.format( "SELECT DISTINCT Artist.Name, Artist.ID FROM Artist JOIN Tune ON Artist.ID=Tune.ArtistID AND Tune.Type=%d AND Tune.SubType=%d ORDER BY Artist.Name", nType, nSubtype );

To clarify, this works:

Cursor c = db.rawQuery( "SELECT rowid _id, Name, Rating FROM Tune ORDER BY Name", null );

Whenever I put rowid _id back into query (A), I get "no such column rowid" exceptions:

String sQuery = String.format( "SELECT rowid _id, Artist.Name, Artist.ID FROM Artist JOIN Tune ON Artist.ID=Tune.ArtistID AND Tune.Type=%d AND Tune.SubType=%d ORDER BY Artist.Name", nType, nSubtype );

What am I doing wrong?

EDIT: I don't even understand what the "rowid _id" does anyway - my SQLite Manager (test tool) doesn't like it either when I have a query with a join. It only seems to work on a simple 1 table query.. So if thats the case.. how do I make this query work without it for SimpleCursorAdapter & ViewBinder?

SparkyNZ
  • 6,266
  • 7
  • 39
  • 80
  • @Luksprog: I actually find it easier when looking through my questions to see which ones were related to Android or not. Is this something that is going to seriously upset a moderator? – SparkyNZ Jul 06 '14 at 05:42
  • There isn't a particular rule against it, it's just adhering to the site's recommendation. It just ads clutter and you could use the extra space to come up with a better title(that would be good on your future revise of the question), *ViewBinder & SimpleCursorAdapter SQL woes* isn't something that clearly shows the problem(also attracting more suitable help). – user Jul 06 '14 at 05:50
  • I see one space between `rowid` and `_id` is that typo mistake? – Shayan Pourvatan Jul 06 '14 at 05:50
  • @shayanpourvatan: Yes its intentional. That means alias rowid as "_id". I have found the solution anyway and I'll post my answer in case anybody else has the same problem. – SparkyNZ Jul 06 '14 at 06:20

1 Answers1

0

The answer was to forget about using rowid and use my own Artist.ID field instead. This will work as long as I alias the field name to _id which SimpleCursorAdapter expects in column 0.

String sQuery = String.format( "SELECT DISTINCT Artist.ID  _id, Artist.Name  FROM Artist JOIN Tune ON Artist.ID=Tune.ArtistID AND Tune.Type=%d AND Tune.SubType=%d ORDER BY Artist.Name", nType, nSubtype );
SparkyNZ
  • 6,266
  • 7
  • 39
  • 80