CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Silkking
Silkking

Reputation: 260

SQLite same column name on join

Using sqlite on android studio, I have two related tables "A" and "B", that both have a column "id". When I make a join on Android Studio, and try to get the value from "id" I get "B.id" whether I put

cursor.getInt(cursor.getColumnIndex("A.id")); // or
cursor.getInt(cursor.getColumnIndex("id"));

I'm considering changing the name of the columns so I can distinguish them easily, but I've seen that sqlite doesn't have any simple thing like "Alter Table "A" rename column", but I would have to duplicate the table with the correct name and deleting the old one so I'm trying to avoid this solution.

Also a solution I thought is to duplicate the column in the query putting something like "Select A.id, * from...", but is something I'd rather avoid.

Any idea why the code I posted might not work? Thanks

Upvotes: 1

Views: 3552

Answers (2)

MikeT
MikeT

Reputation: 56953

With a Cursor you can (albeit it not recommended) use specific offsets (what the getColumnIndex method does is return the offset, the get???? methods all take on offset).

So say the query was SELECT * FROM A JOIN B ON A.id = B.maptoAid and

  • Table A has columns:-
    • id (offset 0) and
    • name (offset 1)
  • and Table B has columns:-
    • id and (offset 2)
    • maptoAid (offset 3) and
    • blah (offset 4)

Then (for the above only, the offsets have to be determined)

to get A.id use cursor.getInt(0); to get B.id use cursor.getInt(2);

Note The use of offsets is discouraged far better to use column aliases (AS) along with the Cursor getColumnIndex method.

  • offsets are not tolerant of changes to the query, that is change the number or order of columns and offsets may have to be re-calculated.
  • names, via the getColumnIndex method (unless duplicating the same name) are tolerant of such changes

Such as 

SELECT a.id AS aid, b.id AS bid .... other columns (aliased or not) .... FROM A JOIN B ON A.id = B.maptoAid

Or easier to code bit with greater overheads

SELECT *, A.id AS aid, B.id  FROM A JOIN B ON A.id = B.maptoAid

and then use :-

cursor.getInt(cursor.getColumnIndex("aid"));
cursor.getInt(cursor.getColumnIndex("bid"));

I get "B.id" whether I put ... and Any idea why the code I posted might not work?

The reason why you get B.id is that the getColumnindex method doesn't finish looping when it finds the column BUT continues the loop, thus returning the last if there are more than one columns with the same name.

Also note (unless fixed) that the Cursor getColumnIndex method is also case sensitive. So cursor.getInt(cursor.getColumnIndex("Aid")) would return -1.

Upvotes: 2

C Perkins
C Perkins

Reputation: 3886

Define an alias on the columns, optionally selecting all other columns with *. These are not mutually exclusive, so the following will work:

SELECT a.id AS a_id, b.id AS b_id, * FROM ...

It will return the aliased columns along with all others, even if the data is redundant:

Columns: a_id, b_id, id, ..., id, ...

Frankly, you can't avoid all solutions. The two solutions that you already listed are about all you have to choose from. Sqlite does not automatically prefix table names to the column names, so there are really no other options.

Upvotes: 2

Related Questions