Hi all - I need help writing a query - not sure how it should go. (Left Outer
Join?!) I need to get ALL Files Posted to ALL Groups that a user is a member
of... Currently I get the groups and the loop over the loops to get the files -
but I would like to condense this to ONE query so I can so some dynamic
sorting... Any ideas? It's a MySQL DB... THANKS IN ADVANCE!!!!
- Nick
<!--- Get the Mailgroups For the user "ESID" --->
<cfquery name="qryGetMailGroups" datasource="#Application.DSN#"
username="#Application.username#" password="#Application.password#">
SELECT EmailGroupLink.MGID, EmailGroup.MGTitle
FROM EmailGroupLink,EmailGroup
WHERE EmailGroupLink.ESID = <cfqueryparam cfsqltype="cf_sql_integer"
value="#SESSION.Auth.ESID#">
AND EmailGroupLink.MGID = EmailGroup.MGID
ORDER by MGTitle
</cfquery>
<cfoutput>
<!--- Loop over Mailgroups and get each groups associated Files --->
<cfloop query="qryGetMailGroups">
<cfquery name="qryGetAccountFiles" datasource="#Application.DSN#"
username="#Application.username#" password="#Application.password#">
SELECT a.FileName, a.UniqDirectory, a.Description, a.PostedDate,
a.AFID, a.Active
FROM Account_Files a,
Account_FileLinks l
WHERE l.MGID = <cfqueryparam cfsqltype="cf_sql_integer"
value="#qryGetMailGroups.MGID#">
AND l.AFID = a.AFID
AND a.Active = 1
ORDER by FileName
</cfquery>
<!--- Output results to the page --->
<cfloop query="qryGetAccountFiles">
<tr>
<td><CF_FileIcon
FILENAME="#qryGetAccountFiles.FileName#"></td>
<td><a href="##"
onClick="window.open('#APPLICATION.SecureURL#/Account/DocumentLoad.cfm?UniqDirectory=#qryGetAccountFiles.UniqDirectory#','myWindow','top:40,left:100,status=no,menubar=no,toolbar=no,location=no');">#qryGetAccountFiles.Description#</a></td>
<td><CF_FileSize
FileName="#qryGetAccountFiles.FileName#"
FileLocation="#APPLICATION.SiteRootDir#\media\AccountFiles\#qryGetAccountFiles.UniqDirectory#\"></td>
<td>#DateFormat(qryGetAccountFiles.PostedDate, "mmm dd yyyy")#</td>
</tr>
</cfloop>
</cfloop>
</cfoutput>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:3918
Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
