Android Question [SOLVED] Strange behaviour of SQL lite - Table names instead of fields

hatzisn · Aug 9, 2018

Hi everyone,

I am facing a problem with an application I am developing for one of my customers. I use a multiple INNER JOIN query which returns some data I need spread in different tables. This is the query:

B4X:

SELECT [70_Categories].[CategoryID], [70_Categories].[Category], [10_Departments].[DepID], [10_Departments].[DepDescription] , [70_Pictures].[PictureFilename]
FROM (([10_Dep_Pics] INNER JOIN
(([70_Categories] INNER JOIN [80_Dep_Categ] ON [70_Categories].[CategoryID] = [80_Dep_Categ].[CategoryID])
INNER JOIN [10_Departments] ON [80_Dep_Categ].[DepID] = [10_Departments].[DepID])
ON [10_Dep_Pics].[DepID] = [10_Departments].[DepID])
INNER JOIN [70_Pictures] ON [10_Dep_Pics].[PicID] = [70_Pictures].[PicID])
WHERE [70_Categories].[CategoryID] = 1 ORDER BY [10_Departments].[DepID]

Normally one would expect the ColumnNames: CategoryID, Category, DepID, DepDescription, PictureFilename

Instead I get the table names instead of the column names above. Thus:
70_Categories, 70_Categories, 10_Departments, 10_Departments, 70_Pictures

I tested the same query against the database in "DBBrowser for SQLite" and it has the same behaviour.
I googled it and so far I found no solution. Has anyone faced it so far? Any suggestions?

Thanks in advance

Jeffrey Cameron · Aug 9, 2018

Try it without escaping the names (e.g. just "70_Categories.CategoryID" without the "[]").

hatzisn · Aug 9, 2018

Thanks Jeffry for the reply, I solved it a moment before you reply by casting (f.e. [70_Categories].[CategoryID] AS CatID).

Gianni Sassanelli · Aug 9, 2018

I think that your query is not well formed
for example
FROM (([70_Categories] INNER JOIN [80_Dep_Categ] ...

Should be
FROM ((Select [70_Categories].* FROM [70_Categories]
INNER JOIN [80_Dep_Categ] ...

you must use the square bracked because your table name start with number
70_categories ===> this need [70_Categories]
if your tables named categories_70 you don't need square bracked

Generally you need to use the square bracked when
1) fieldname or tablename contains space or specialchar [Field 1] or [Field.num]
2) fieldname or tablename starts with number or specialchar [70_categories] or [70 Categories]

I'll give you some advice, to write your query in a more readable and hence easier to verify use this syntax:

B4X:

dim stmt as String
stmt = $"
SELECT
    SQ1.*
FROM (
    SELECT
           C7.*
           ,C8.*
    FROM [70_Categories]          AS C7
    INNER JOIN [80_Dep_Categ] AS C8 ON C7.CategoryID = C8.CategoryID
          ) AS SQ1   /*(subquery1)*/
LEFT JOIN (
    SELECT
           C6.*
    FROM [60_Categories]          AS C6
    INNER JOIN [90_Dep_Categ] AS C9 ON C9.CategoryID = C6.CategoryID
          ) AS SQ2   /*(subquery2)*/     ON
    SQ2.joincondition = SQ1.JoinCondition
WHERE
   SQ1.WhereCondition
  AND SQ2.WhereCondition
"$

Declare CR as Cursor
Cr = SQL.ExecQuery(stmt)

Android Question [SOLVED] Strange behaviour of SQL lite - Table names instead of fields

hatzisn

Expert

Jeffrey Cameron

Well-Known Member

hatzisn

Expert

Gianni Sassanelli

Active Member

Similar Threads

Android Question [SOLVED] Strange behaviour of SQL lite - Table names instead of fields

hatzisn

Expert

Jeffrey Cameron

Well-Known Member

hatzisn

Expert

Gianni Sassanelli

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency