12

I've got a fairly complicated query (multiple joins) on a normalized sqlite database. The query does a SELECT * to enable some automated attribute selection logic (so I can't eliminate the "*")

The problem I am having is that my result set contains multiple columns with the same attribute name. For example, one attribute common to each table in the query is "_id". When I go to call "cursor.getColumnIndex("_id")" the value returned is always the index of the last "_id" attribute in the result set column list (i.e. not the one I want). I'd love to be able to use my SQL alias prefixes like cursor.getColumnIndex("A._id") but that is not working.

QUESTIONs

  • It appears that cursor.getColumnIndex(AttributeName) returns the index of the last "AttributeName". Can anyone confirm this?
  • Also, any suggestions on how return the index of the 1st attribute with "AttributeName"? or better the Xth attribute having "AttributeName"?
General Grievance
  • 4,555
  • 31
  • 31
  • 45
celoftis
  • 363
  • 3
  • 6
  • 18

3 Answers3

8

You can do this:

SELECT _id as myID, * FROM myTable

This means the _id field will appear twice for each table in your results, but one of the two columns will have a unique name which should enable you to find it.

Graham Borland
  • 60,055
  • 21
  • 138
  • 179
1

Unfortunately the documentation doesn't mention anything about what you need to do, so I am assuming it cannot be done.

However, you say

The query does a SELECT * to enable some automated attribute selection logic (so I can't eliminate the "*")

What is this 'automated attribute selection logic' you speak of? Why do you require this?

Jack
  • 9,156
  • 4
  • 50
  • 75
  • its part of a survey data collection system - the logic assists in dynamically packaging appropriate (each device may have different rules for determining what "appropiate" is...) data for transmission back to a central data store. This logic is not something that I have liberty to modify... – celoftis Nov 01 '11 at 13:26
  • Ah that is unfortunate, have you trying prefixing with the complete table name? Such as users._id ? – Jack Nov 01 '11 at 13:31
-3

An oder solution is:

"SELECT tableName.columnName FROM tableName"

and then do the same with:

cursor.getColumnIndex("tableName.columnName");

This is what MS-Access does. You can create a query and then see the generated SQL code (simply going to 'view' menu and selecting 'SQL view' from your query dessign window)

Xavier
  • 1