No, it SHOULD return SessionTypeName. The 3rd filed is SessionTypeID. But in the past this query has always returned a SessionTypeName field.
Ok, you didn't specify it returned SessionTypeID as fieldname. Yeah, that's wrong.
And even if it's wrong, overruling it with " as otherfield" should have worked. I'm wondering if this is really a SQLite3 problem or a Lazarus/FPC problem.
Could you try the following:
JOIN SessionType on SessionType.SessionTypeID=Session.SessionTypeID
(to see if the USING way is at fault)
Normally I would do something like this:
SELECT s.SessionID, s.Identifier, t.SessionTypeName, c.CameraID, s.Date
FROM Session s
JOIN SessionType t on t.SessionTypeID=s.SessionTypeID
JOIN Camera c on c.CameraID=s.CameraID
ORDER BY s.Identifier, t.SessionTypeName
That way there is no confusion where the fields are coming from.
Running this in the sqlite3 shell produces the following:
...> ORDER BY Identifier,SessionType.SessionTypeName;
111|120 Film-10|120 Film|5|2000-06-01
112|120 Film-11|120 Film|6|1999-04-01
Does the sqlite3 shell not provide fieldnames ???
What is the third field named there??
If the third field is named SessionTypeName then SQLite3 is not the problem.
What version of SQLite3 and Lazarus are you using?
Has this problem surfaced since a new version of one of the two?
(edit: also using using() gives me the correct result so we need to now versionnumbers.)