Hi Nick, Im not a big MySQL DB guy, but meanwhile you can try this and see
if it works for you:
******************************
SELECT DISTINCT
EmailGroupLink.MGID
,EmailGroup.MGTitle
,Account_Files.FileName
,Account_Files.UniqDirectory
,Account_Files.Description
,Account_Files.PostedDate
,Account_Files.AFID
,Account_Files.Active
FROM EmailGroupLink
INNER JOIN EmailGroup ON EmailGroupLink.MGID = EmailGroup.MGID
INNER JOIN Account_FileLinks ON Account_FileLinks.MGID = EmailGroupLink.MGID
LEFT JOIN Account_Files ON Account_Files.AFID = Account_FileLinks.AFID
WHERE EmailGroupLink.ESID = <cfqueryparam cfsqltype="cf_sql_integer"
value="#SESSION.Auth.ESID#">
AND Account_Files.Active = 1
ORDER BY Account_Files.FileName
******************************
Regards,
Misha
On Mon, Aug 11, 2008 at 3:55 PM, Nick Sweeney <[EMAIL PROTECTED]>wrote:
> 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:3919
Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
