Run second sqlite query before finishing the previous query?

Just starting out? Need help? Post your questions and find answers here.
User avatar
doctorized
Addict
Addict
Posts: 854
Joined: Fri Mar 27, 2009 9:41 am
Location: Athens, Greece

Run second sqlite query before finishing the previous query?

Post by doctorized »

I some cases I want to run a sqlite query, get the data and, according to the data retrieved, I need to run a second query before running FinishDatabaseQuery() for the first one. I can't join the two queries into one as the second query will bring multiple data rows for every row of the first query. See the following example. It is wrong but shows the logic.

Code: Select all

DatabaseQuery(#db, "SELECT id, Name, Address, OrderCount FROM People Where Address LIKE '%AVENUE%'")
While NextDatabaseRow(#db)
	;do some work here
	If GetDatabaseLong(#db, 3) > 0
		DatabaseQuery(#db, "SELECT OrderDetails FROM Orders Where userid = '" + GetDatabaseString(#db, 0) + "'")
		While NextDatabaseRow(#db)
			;do some work with those data
		Wend
		FinishDatabaseQuery()
	EndIf
Wend	
FinishDatabaseQuery()
So, is there anything that I can do?
User avatar
Kiffi
Addict
Addict
Posts: 1353
Joined: Tue Mar 02, 2004 1:20 pm
Location: Amphibios 9

Re: Run second sqlite query before finishing the previous query?

Post by Kiffi »

You can use an Inner Join:

Code: Select all

Select id, Name, Address, OrderCount FROM People
Inner Join OrderDetails On OrderDetails.userid = People.id
Where People.OrderCount > 0 And People.Address LIKE '%AVENUE%'
(untested)
Hygge
infratec
Always Here
Always Here
Posts: 6818
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Run second sqlite query before finishing the previous query?

Post by infratec »

It is not possible this way.
But ...

Improve your SQL.
It is also possible with WITH

Code: Select all

UseSQLiteDatabase()

DB = OpenDatabase(#PB_Any, ":memory:", "", "")
If DB
  DatabaseUpdate(DB, "CREATE TABLE People (Id INTEGER, Name TEXT, Address TEXT, OrderCount INTEGER)")
  DatabaseUpdate(DB, "INSERT INTO People VALUES (1, 'AName', 'First Road', 1)")
  DatabaseUpdate(DB, "INSERT INTO People VALUES (2, 'BName', 'First Avenue', 2)")
  DatabaseUpdate(DB, "INSERT INTO People VALUES (3, 'CName', 'Second Avenue', 3)")
  
  DatabaseUpdate(DB, "CREATE TABLE Orders (Id INTEGER, UserId INTEGER, OrderDetails TEXT)")
  DatabaseUpdate(DB, "INSERT INTO Orders VALUES (1, 2, 'Toast2')")
  DatabaseUpdate(DB, "INSERT INTO Orders VALUES (2, 2, 'Bread2')")
  DatabaseUpdate(DB, "INSERT INTO Orders VALUES (3, 1, 'Bread1')")
  DatabaseUpdate(DB, "INSERT INTO Orders VALUES (4, 1, 'Marmalade1')")
  DatabaseUpdate(DB, "INSERT INTO Orders VALUES (5, 3, 'Chocolate')")
  
  SQL$ = "WITH Temp AS (SELECT id, Name, Address, OrderCount FROM People Where Address LIKE '%Avenue%') "
  SQL$ + "SELECT P.Name, O.OrderDetails FROM Orders O, Temp P Where O.userid = P.id"
  
  If DatabaseQuery(DB, SQL$)
    While NextDatabaseRow(DB)
      Debug GetDatabaseString(DB, 0) + " " + GetDatabaseString(DB, 1)
    Wend
  Else
    Debug SQL$
    Debug DatabaseError()
  EndIf
  
  CloseDatabase(DB)
EndIf
User avatar
doctorized
Addict
Addict
Posts: 854
Joined: Fri Mar 27, 2009 9:41 am
Location: Athens, Greece

Re: Run second sqlite query before finishing the previous query?

Post by doctorized »

Non of these can actually help as the real usage of the query (and the query itself) is much more complicated. I have a query with 7-8 inner and outer joins as I need to retrieve data from multiple tables. Those data are presented in custom buttons and the data retrieved from the new query that I want to run, will be presented with a short way on the right down corner of the button. Both solutions create multiple rows with the same data in most columns. That means I have to do many additional checks to find out if I have a row that the basic data (id, name, etc) are already shown and I have additional data to add to the existing ones.
User avatar
skywalk
Addict
Addict
Posts: 3972
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: Run second sqlite query before finishing the previous query?

Post by skywalk »

As mentioned, you can do a lot with better SQL logic. You could create temp tables in memory from sequential queries and then make your final select. Or massage your original table design to support your most frequent queries. Example, you always add col a,b,c, then make a sumcol. Extreme data normalization does not always yield the ripest fruit.
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
#NULL
Addict
Addict
Posts: 1440
Joined: Thu Aug 30, 2007 11:54 pm
Location: right here

Re: Run second sqlite query before finishing the previous query?

Post by #NULL »

(I did not read the previous 2 posts yet)

Code: Select all

;do some work here
Since you want to process customers independently of wether they have orders, a LEFT JOINT could be what you want.
Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).
And you can use a map with ids as keys to handle the duplicate customer data:
(And I would not trust a column OrderCount if I see it :) )

Code: Select all

UseSQLiteDatabase()

DB = OpenDatabase(#PB_Any, ":memory:", "", "")
If DB
  DatabaseUpdate(DB, "CREATE TABLE People (Id INTEGER, Name TEXT, Address TEXT, OrderCount INTEGER)")
  DatabaseUpdate(DB, "INSERT INTO People VALUES (1, 'A', 'First Road', 1)")
  DatabaseUpdate(DB, "INSERT INTO People VALUES (2, 'B', 'First Avenue', 2)")
  DatabaseUpdate(DB, "INSERT INTO People VALUES (3, 'C', 'street', 0)")
  DatabaseUpdate(DB, "INSERT INTO People VALUES (4, 'D', 'Second Avenue', 3)")
  
  DatabaseUpdate(DB, "CREATE TABLE Orders (Id INTEGER, UserId INTEGER, OrderDetails TEXT)")
  DatabaseUpdate(DB, "INSERT INTO Orders VALUES (1, 2, 'Toast_B')")
  DatabaseUpdate(DB, "INSERT INTO Orders VALUES (2, 2, 'Bread_B')")
  DatabaseUpdate(DB, "INSERT INTO Orders VALUES (3, 1, 'Bread_A')")
  DatabaseUpdate(DB, "INSERT INTO Orders VALUES (4, 1, 'Marmalade_A')")
  DatabaseUpdate(DB, "INSERT INTO Orders VALUES (5, 4, 'Chocolate_D')")
  
  Structure Order
    Id.i
    UserId.i
    OrderDetails.s
  EndStructure
  
  Structure customer
    Id.i
    Name.s
    Address.s
    OrderCount.i
    oCnt.i
    Map Orders.Order()
  EndStructure
  
  NewMap Customers.Customer()
  
  SQL$ = ""
  SQL$ + "SELECT "
  SQL$ + "p.Id As p_Id, Name, Address, OrderCount, "
  SQL$ + "od.Id As od_Id, UserId, OrderDetails "
  SQL$ + "FROM People As p LEFT JOIN Orders As od "
  SQL$ + "ON p.Id = od.UserId "
  SQL$ + "ORDER BY p.Id, od.Id;"
  
  If DatabaseQuery(DB, SQL$)
    
    While NextDatabaseRow(DB)
      
      Debug GetDatabaseString(DB, DatabaseColumnIndex(DB, "Name")) + ": " + GetDatabaseString(DB, DatabaseColumnIndex(DB, "OrderDetails"))
      
      p_Id$ = GetDatabaseString(DB, DatabaseColumnIndex(DB, "p_id"))
      p_Id = Val(p_Id$)
      Customers(p_Id$)\Id = p_Id
      Customers(p_Id$)\Name = GetDatabaseString(DB, DatabaseColumnIndex(DB, "Name"))
      Customers(p_Id$)\Address = GetDatabaseString(DB, DatabaseColumnIndex(DB, "Address"))
      Customers(p_Id$)\OrderCount = Val(GetDatabaseString(DB, DatabaseColumnIndex(DB, "OrderCount")))
      od_Id$ = GetDatabaseString(DB, DatabaseColumnIndex(DB, "od_Id"))
      od_Id = Val(od_Id$)
      If od_Id$
        Customers(p_Id$)\Orders(od_Id$)\Id = od_Id
        Customers(p_Id$)\Orders(od_Id$)\UserId = Val(GetDatabaseString(DB, DatabaseColumnIndex(DB, "UserId")))
        Customers(p_Id$)\Orders(od_Id$)\OrderDetails = GetDatabaseString(DB, DatabaseColumnIndex(DB, "OrderDetails"))
      EndIf
    Wend
    Debug "----"
    
    ForEach Customers()
      Customers()\oCnt = MapSize(Customers()\Orders())
      
      Debug Customers()\Name + "(" + Customers()\oCnt + "/" + Customers()\OrderCount + ")"
      
      ;do some work here
      
      If Customers()\oCnt > 0
        ForEach Customers()\Orders()
          Debug "    " + Customers()\Orders()\OrderDetails
          
          ;do some work with those data
          
        Next
      Else
        Debug "    [no orders]"
      EndIf
    Next
    Debug "----"
  Else
    Debug SQL$
    Debug DatabaseError()
  EndIf
  
  CloseDatabase(DB)
EndIf
Last edited by #NULL on Sat Oct 09, 2021 3:09 pm, edited 1 time in total.
User avatar
Paul
PureBasic Expert
PureBasic Expert
Posts: 1243
Joined: Fri Apr 25, 2003 4:34 pm
Location: Canada
Contact:

Re: Run second sqlite query before finishing the previous query?

Post by Paul »

Or do something as simple as open the database a 2nd time for temp queries...

Code: Select all

OpenDatabase(#db,"mydatabase.sqlite")
OpenDatabase(#qry2,"mydatabase.sqlite")

DatabaseQuery(#db, "SELECT id, Name, Address, OrderCount FROM People Where Address LIKE '%AVENUE%'")
While NextDatabaseRow(#db)
	;do some work here
	id$=GetDatabaseString(#db, 0)
	
	If GetDatabaseLong(#db, 3) > 0
		DatabaseQuery(#qry2, "SELECT OrderDetails FROM Orders Where userid = '" +id$+ "'")
		While NextDatabaseRow(#qry2)
			;do some work with those data
		Wend
		FinishDatabaseQuery(#qry2)
	EndIf
Wend	
FinishDatabaseQuery(#db)
Image Image
User avatar
doctorized
Addict
Addict
Posts: 854
Joined: Fri Mar 27, 2009 9:41 am
Location: Athens, Greece

Re: Run second sqlite query before finishing the previous query?

Post by doctorized »

Nice ideas guys but I decided to break my complex query to smaller/simpler ones. As skywalk mentions, I have to change the logic. It is better having 7-8 separate queries retrieving the data in levels rather than having a complex one that is hard to maintain. Thank you very much for your help. You learnt me new stuff.
Post Reply