SQLite DB INNER JOIN problem

I’m using Xojo 2014r2.1 with a SQLite DB on a desktop project. I’m having issues with an INNER JOIN:

I use the following code to pull all fields from two tables, tblImages and tblQuest:

rs=db.SQLSelect(“SELECT tblImages., tblQuest. FROM tblImages INNER JOIN tblQuest ON tblImages.fldQuestID = tblQuest.ID WHERE fldQuestID = '” + lvQuest.Cell(lvQuest.ListIndex,0) + “’”)

That works fine. The problem is both tables have a field called ID and I want to assign the ID field from the tblImages to the HelpTag of an Image Well control. If I do:

picThumb1.HelpTag = rs.Field(“ID”).StringValue

it succeeds but it assigns the ID field from tblQuest which I do not want. If I do:

picThumb1.HelpTag = rs.Field(“tblImages.ID”).StringValue

I get a NilObjectException.

If I look at the variable in the stack it shows both tblImages.ID and tblQuest.ID have values. Neither is Nil.

Am I missing something?

All columns in the select statement need to be unique. You’ll need to specify the columns from one or both tables and use an alias for one of the ID columns.

For example
rst = db.sqlselect("SELECT tblImages.id AS ImageID, tblQuest.* …

HTH

Wayne

@Wayne Golding that worked perfectly. Thank you.

Curios, is that unique to Xojo or SQLite? I’ve used the other method in MS SQL and Access many times with no issue.

Not unique to Xojo or SQLite as far as I know.

It may be “unique” to Sqlite, although MySql does the same. The result set contains 2 fields with the same name. It does not distinguish them by table. You can observe this in a query browser. If you knew the field order, you could get both values by index, but the correct approach is to alias any fields that have the same name.

That sounds reasonable. Thanks all.

Avoid positional access to recordsets, try to use named access as Wayne proposed. Any external DB layout change could initiate an unexpected behavior, or worst, a silent fail like updating a wrong DB field.