Android, Sqlite and UNION query

Problems with WiFi, SQLite ,Bluetooth, WiMax, Proxies, etc...

Android, Sqlite and UNION query

Postby ThePhoenix » Thu Feb 18, 2010 12:12 am

Hi all, I am a complete noob in both Java and Android, and this is my first application, so please be very basic in any assistance you give me, as I'm very likely to not understand. :-)

Using Android 1.5, I have a need to UNION three queries and have discovered that I can do this using rawQuery.

Here is a code snippet from my app (if this is really bad, please don't be too scathing!)

public long recordCount() {
String sqlString = "SELECT " + KEY_ROWID + " FROM " + DATABASE_TABLE + " WHERE status <> 'C'" +
" UNION SELECT " + KEY_ROWID + " FROM " + DATABASE_TABLE + " WHERE status <> 'X'" +
" UNION SELECT " + KEY_ROWID + " FROM " + DATABASE_TABLE + " WHERE status <> 'S'" ;

Cursor c = jDb.rawQuery(sqlString, null);

return c.getCount();

The table holds 2 records, and as no DISTINCT keyword is used, I fully expected the record count to return 6, but the result comes back as 2.

Don't worry about the odd nature of this sql, it's not what the final selections are going to be, I just repeated the initial SELECT for each UNION as an easy test. I ended up with different WHERE criteria while clutching at straws as to why I was not getting the count of 6 that I was expecting. The actual value of 'status' in both of the two records is blank.

I guess I have three questions:
Why 2 when I think there should be 6 records in the set?
Is there a better / more efficient way of approaching a UNION query in Android?
and lastly I seem to remember getting a failure if I use WHERE status = ' ' What is wrong with doing this?

Thanks in advance.

Paul
ThePhoenix
Junior Developer
Junior Developer
 
Posts: 10
Joined: Wed Feb 17, 2010 11:43 pm
Location: England

Top

Solved it.

Postby ThePhoenix » Sat Feb 20, 2010 9:19 pm

Just for anyone that might be interested, I remembered that to do what I have described above, UNION alone won't do it. It has to be UNION ALL.

Made this mod and all is well.
ThePhoenix
Junior Developer
Junior Developer
 
Posts: 10
Joined: Wed Feb 17, 2010 11:43 pm
Location: England

Top

Return to Networking & Database Problems

Who is online

Users browsing this forum: No registered users and 4 guests