Android Question SQL Lite - No Such Column

DawningTruth

Active Member
Licensed User
I am trying to do a join on 2 different SQLite tables. Here is my SQL Statement:

B4X:
Select * FROM tbl_items WHERE tbl_categories.cl_reference = 'cat01' AND tbl_categories.cl_song = tbl_items.cl_song ORDER BY cl_last_access_time ASC

I keep getting the following error:

android.database.sqlite.SQLiteException: no such column: tbl_categories.cl_reference (Sqlite code 1):

I isolated the query a bit and it is giving the same error for the other columns as well.

Any suggestions?
 

RB Smissaert

Well-Known Member
Licensed User
Longtime User
I am trying to do a join on 2 different SQLite tables. Here is my SQL Statement:

B4X:
Select * FROM tbl_items WHERE tbl_categories.cl_reference = 'cat01' AND tbl_categories.cl_song = tbl_items.cl_song ORDER BY cl_last_access_time ASC

I keep getting the following error:

android.database.sqlite.SQLiteException: no such column: tbl_categories.cl_reference (Sqlite code 1):

I isolated the query a bit and it is giving the same error for the other columns as well.

Any suggestions?

You need to specify the join (plenty of examples on the net, usually you will need an inner join) and also you need to specify where your * comes from if there are multiple tables in the query.
So you construction will need to be something like this:

B4X:
select a.*, b.field1 from tableA as a inner join tableB as b on(a.id = b.id)

RBS
 
Upvote 0

rraswisak

Active Member
Licensed User
Use JOIN clause as RBS suggested,

SELECT tbl_items.*
FROM tbl_items INNER JOIN tbl_categories ON tbl_items.cl_song=tbl_categories.cl_song AND tbl_categories.cl_reference='cat01'
ORDER BY tbl_items.cl_last_access_time ASC
 
Upvote 0
Top