DatabaseQuery within Database Query

mrjiles · Post by **mrjiles** » Mon May 05, 2008 4:10 pm

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?

Post by **Fred** » Mon May 05, 2008 4:14 pm

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.

mrjiles · Post by **mrjiles** » Mon May 05, 2008 4:24 pm

Nested! That's the word I was looking for! Thanks Fred.

Rook Zimbabwe · Post by **Rook Zimbabwe** » Mon May 05, 2008 8:24 pm

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...

mrjiles · Post by **mrjiles** » Mon May 05, 2008 9:05 pm

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.

Edwin Knoppert · Post by **Edwin Knoppert** » Mon May 05, 2008 9:44 pm

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..

didier69 · Post by **didier69** » Fri May 09, 2008 4:43 pm

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 ?

mrjiles · Post by **mrjiles** » Fri May 09, 2008 5:57 pm

@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.

mrjiles · Post by **mrjiles** » Fri May 09, 2008 6:14 pm

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

didier69 · Post by **didier69** » Fri May 09, 2008 6:49 pm

@mirjiles: I was thinking to do the same as you posted here.
Thanks for your example.