[thelist] Nested Queries

David.Cantrell at Gunter.AF.mil David.Cantrell at Gunter.AF.mil
Wed Aug 21 12:42:01 CDT 2002


>I would be interested in seeing your query as well.

Well, I'm no DB expert, and since I don't know which DB you are using I'll
go with SQL Server. Your best bet in that case is to use stored procedures
to encapsulate the selection logic.

This recursion currently will list the same e-mail multiple times, so if all
you want as the final result is a list of e-mails you'd need to push e-mail
addresses onto a stack as you pick them out, then when you finish retrieving
them go through the stack popping off e-mails and sending the messages.

If you need to know how to build a stack in VBScript let me know and I'll
see if I can dig up the link. I think http://www.4guysfromrolla.com did a
Stack class in VBScript a couple years back, it works pretty well IIRC.

Let me know what you think...
-dave

---BEGIN CODE SAMPLE---

	============================================
	List groups belonging to the specified group
	============================================
	create proc get_child_groups
		@group_id int IN
	as
	select child_group_id from tGroupEmails
	where group_id = @group_id

	==============================
	List emails in specified group
	==============================
	create proc get_group_emails
		@group_id int IN
	as
	select email from tGroupEmails
	where group_id = @group_id

	========================
	Loop through list in ASP
	========================
	dim sOutput  : sOutput  = ""
'final output string
	dim iGroupId : iGroupId = Request( "group_id" )		'group id
selected by user

	dim goConn : Set goConn = Server.CreateObject(
"Adodb.Connection.2.5" )
	goConn.Open "connection-string"
	dim oGroupRs : Set oGroupRs = goConn.Execute( "exec
get_groups_in_group " & iGroupId )
	Do until oGroupRs.Eof
		sOutput = sOutput & Groups( oGroupRs( "child_group_id" ),
sOutput, goConn )
		oGroupRs.MoveNext
	Loop
	oGroupRs.Close
	Set oGroupRs = Nothing
	goConn.Close
	Set goConn = Nothing

	response.write sOutput

	-------------------------------------------
	Function Groups( iGroupId, sOutput, oConn )
		If IsNull( sOutput ) then sOutput = ""

		'extracts e-mail addresses from current group and adds to
output string
		Set oEmails = oConn.Execute( "exec get_group_emails " &
iGroupId )
		Do until oEmails.Eof
			sOutput = sOutput & oEmails( "email" )
			oEmails.MoveNext
		Loop
		oEmails.Close

		'extracts child groups from current group and recurses for
each child
		dim oGroups : Set oGroups = oConn.Execute( "exec
get_child_groups " & iGroupId )
		dim iChildGroupId
		Do until oGroups.Eof
			sOutput = sOutput & Groups( oGroups(
"child_group_id" ), sOutput, oConn )
			oGroups.MoveNext
		Loop
	End Function

---END CODE SAMPLE---



More information about the thelist mailing list