Page 1 of 1
DatabaseQuery within Database Query
Posted: Mon May 05, 2008 4:10 pm
by mrjiles
I'm trying to perform a query within a query. Basically, the first query returns a category name, and for every category name I want to find all documents in that category. Something like this:
Code: Select all
If OpenDatabase(#dbaDatabase, "hash.db", "", "", #PB_Database_SQLite)
DatabaseQuery(#dbaDatabase, "SELECT Name FROM Categories ")
While NextDatabaseRow(#dbaDatabase)
category$ = GetDatabaseString(#dbaDatabase, 0)
html$ + category$ + "<br />"
DatabaseQuery(#dbaDatabase, "SELECT " + category$ + " FROM Documents")
While NextDatabaseRow(#dbaDatabase)
doc$ = GetDatabaseString(#dbaDatabase, 0)
html$ + "<b>" + doc$ + "</b><br /><br />"
Wend
Wend
CloseDatabase(#dbaDatabase)
EndIf
But it stops after the first query. Any ideas if this is possible and/or workarounds?
Posted: Mon May 05, 2008 4:14 pm
by Fred
You will have to do it using SQL nested query (if you database support it). Something like: SELECT * FROM table WHERE id = 'SELECT id FROM anothertable'. Check an SQL tutorial for more infos.
Posted: Mon May 05, 2008 4:24 pm
by mrjiles
Nested! That's the word I was looking for! Thanks Fred.
Posted: Mon May 05, 2008 8:24 pm
by Rook Zimbabwe
Jiles:
If you are using an ACCESS DB of 2000 flavor onwards... you can create QUERIES that you can call exactly like SQL statements... I use them in my POS program to display TIPS per waiter and I can run SQL on the QUERY to further refine it! I can target down to a single WAITER of instance...
Posted: Mon May 05, 2008 9:05 pm
by mrjiles
Rook, I like the new SQLite that is incorporated into PB, so I'm using that. My DB usage is basic so I don't need anything fancy. I'm developing a web-gui help system for my employer where all of the documentation is stored in a database. Thanks for the tip though, I may look into this for larger projects.
Posted: Mon May 05, 2008 9:44 pm
by Edwin Knoppert
when using MDB's or T-SQL you can use something like:
SELECT
T1.MyField1
, T2.ID
, ( SELECT ID FROM TABLE2 ) As T2
* FROM TABLE1 AS T1
--
A nonsense query in this case of course..
Posted: Fri May 09, 2008 4:43 pm
by didier69
Fred wrote:You will have to do it using SQL nested query (if you database support it). Something like: SELECT * FROM table WHERE id = 'SELECT id FROM anothertable'. Check an SQL tutorial for more infos.
I have apparently the same problem but not in the same order.
I need to do another sql query to find information from other
tables in the first loop of NextDatabaseRow().
What do you mean with your message ? There can be only one
"cursor" on a query result at the same time ? I can't define
two cursors to two query result at the same time ?
Posted: Fri May 09, 2008 5:57 pm
by mrjiles
@didier69: No, each query must be self contained basically. Here is the code I wrote to do just this (sorry I'm @ work and can't clean it up):
Code: Select all
Procedure DisplayTOC()
Protected string$ = "", num = 0, template, html$ = "", Dim category_name$(num), Dim item_number$(num)
If OpenDatabase(#dbaDatabase, "hash.db", "", "", #PB_Database_SQLite)
DatabaseQuery(#dbaDatabase, "SELECT * FROM Categories")
While NextDatabaseRow(#dbaDatabase)
category_name$(num) = GetDatabaseString(#dbaDatabase, 1)
num + 1
Redim category_name$(num)
Wend
For x = 0 To (num-1)
If DatabaseQuery(#dbaDatabase, "SELECT * FROM Documents WHERE Category = '" + category_name$(x) + "'")
html$ + "<font class=" + Chr(34) + "cattitle" + Chr(34) + ">" + category_name$(x) + "</font><br />"
While NextDatabaseRow(#dbaDatabase)
html$ + "<a href=" + Chr(34) + "hash.doc:" + GetDatabaseString(#dbaDatabase, 0) + Chr(34) + ">" + GetDatabaseString(#dbaDatabase, 2) + "</a> <br />"
Wend
EndIf
Next
template = OpenFile(#PB_Any, "toc.tem")
If template
While Eof(template) = #False
string$ + ReadString(template)
Wend
string$ = ReplaceString(string$, "{categories}", html$)
html$ = string$
CloseFile(template)
EndIf
SetGadgetItemText(#webContent, #PB_Web_HtmlCode, html$)
CloseDatabase(#dbaDatabase)
EndIf
EndProcedure
Basically, first query is saved to an array. Second query loops through array for it's query.
Example: Query #1 retrieves states and saves to array. End query. Start loop, each state is queried for capital city. End query.
Posted: Fri May 09, 2008 6:14 pm
by mrjiles
Okay, here is something cleaned up. I am in no way a PB Pro so there is probably something messed up in here. Also, I did not test this but it gives a better idea than my first code.
Code: Select all
num = 0
Dim state$(num) ; Make the array
If OpenDatabase(0, "yourdatabasefile.db, "", "", #PB_Database_SQLite) ; open database
DatabaseQuery(0, "SELECT * FROM UnitedStates") ; query
While NextDatabaseRow(0) ; for each row
state$(num) = GetDatabaseString(0, 0) ; save the sate in array
num = num+1 ; increase array
Redim state$(num) ; increase array
Wend
; This will loop each for each state, testing every row until all states have been passed
For x=0 To (num-1) ; for 0 to total states basically
If DatabaseQuery(0, "SELECT * FROM UnitedStates WHERE State = '" + state$(num) + "'")
While NextDatabaseRow(0)
; this is where the GetDatabaseString would be
Wend
EndIf
Next
EndIf
Posted: Fri May 09, 2008 6:49 pm
by didier69
@mirjiles: I was thinking to do the same as you posted here.
Thanks for your example.