Problem with SQLite query

[doctorized]

I make an app that handles school library. I try to run the query:

Code: Select all

Select L.book_id, B.Title, count(*) from Lendings L left join Books B on L.book_id = B.id where L.stud_id like '2023-2024%' group by L.book_id order by count(*) DESC

and I get error:

Code: Select all

near "(": syntax error

The exactly same query with DB Brower for SQLite is running just fine. Any suggestions why?

[ChrisR]

No tables to test it but to get L.book_id, B.Title, count(*), Group By should probably use the same fields: L.book_id, B.Title

Code: Select all

Select L.book_id, B.Title, count(*) from Lendings L left join Books B on L.book_id = B.id where L.stud_id like '2023-2024%' group by L.book_id, B.Title order by count(*) DESC

[doctorized]

I have:

Code: Select all

CREATE TABLE Books (id TEXT PRIMARY KEY, Title TEXT, Writer TEXT, Publisher TEXT, Year INT, Copies TINYINT, ISBN TEXT)
CREATE TABLE Lendings (book_id TEXT, stud_id TEXT, LDate DATE, Returned TINYINT, RDate DATE)

Your query returns the same error.
On the other hand,

Code: Select all

select count(*) from books

runs just fine.

[mk-soft]

Without Data no error here

Code: Select all

UseSQLiteDatabase()

#Database = 0

Define dbfile.s, sql.s, r1


Procedure CheckDatabaseUpdate(Database, Query$)
 Result = DatabaseUpdate(Database, Query$)
 If Result = 0
    Debug DatabaseError()
 EndIf
 
 ProcedureReturn Result
EndProcedure

Procedure CheckDatabaseQuery(Database, Query$)
 Result = DatabaseQuery(Database, Query$)
 If Result = 0
    Debug DatabaseError()
 EndIf
 
 ProcedureReturn Result
EndProcedure

dbfile = ":memory:"

If OpenDatabase(#Database, dbfile, "", "")
  Debug "Create Tables ..."
  sql = "CREATE TABLE Books (id TEXT PRIMARY KEY, Title TEXT, Writer TEXT, Publisher TEXT, Year INT, Copies TINYINT, ISBN TEXT)"
  CheckDatabaseUpdate(#Database, sql)
  
  sql = "CREATE TABLE Lendings (book_id TEXT, stud_id TEXT, LDate DATE, Returned TINYINT, RDate DATE)"
  CheckDatabaseUpdate(#Database, sql)
  
  Debug "Query ..."
  ;sql = "select count(*) from books"
  sql = "Select L.book_id, B.Title, count(*) from Lendings L left join Books B on L.book_id = B.id where L.stud_id like '2023-2024%' group by L.book_id order by count(*) DESC"
  If CheckDatabaseQuery(#Database, sql)
    Debug "Result ..."
    While NextDatabaseRow(#Database)
      Debug GetDatabaseString(#Database, 0)
    Wend
    FinishDatabaseQuery(#Database)
  EndIf
  Debug "End ..."
  CloseDatabase(#Database)
EndIf