[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