Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.
<!--- query getDocumentsandFiles: select columns from tables tbl_CEP_Documents D, tbl_CEP_Document_Topic T, tbl_Document_Has_Topic H, tbl_CEP_files F --->
<!--- to display CEP Document Title, Author, Abstract, Publication Date; and CEP Files associated with those documents; and download links to those files --->
<cfquery name="getDocumentsandFiles" datasource="#ds#">
SELECT D.DocumentID
, D.DocumentTitle
, D.DocumentType
, D.DocumentAuthor
, D.DocumentAbstract
, D.DocumentPublicationDate
, T.DocumentTopicID
, T.DocumentTopic
, H.DocumentID
, H.DocumentTopicID
, F.DocumentID
, F.FileID
, F.FileName
, F.FileLinkText
, F.FileExtension
, F.FileType
, F.FileSize
, F.IsPressRelease
FROM tbl_CEP_Documents D, tbl_CEP_Document_Topic T, tbl_Document_Has_Topic H, tbl_CEP_files F
WHERE D.DocumentID = H.DocumentID
AND D.DocumentID = F.DocumentID
AND F.IsPressRelease = 1
ORDER BY D.DocumentPublicationDate DESC, D.DocumentTitle, D.DocumentID, F.FileName
</cfquery>
<h1>Press Releases</h1>
<!--- begin output from query getDocumentsandFiles; list Document Title, Publication Date; and CEP 'Press Release' Files associated with those documents; and download links to those files --->
<cfoutput query="getDocumentsandFiles" group="DocumentTitle">
<cfif CurrentRow MOD 2 IS 1>
<cfset bgcolor="##ffffff">
<cfelse>
<cfset bgcolor="##f7f5f5">
</cfif>
<!--- set bgcolor --->
<div style="background-color:#bgcolor#;">
<!--- set padding --->
<div style="padding:5px;">
<h3>#DocumentTitle#</h3>
<p class="documentText">
<em>Published:</em> <span class="black">#DateFormat(DocumentPublicationDate, "mmmm d, yyyy")#</span><br />
<!--- output group on DocumentID --->
<cfoutput group="DocumentTitle">
<!--- output of query filename --->
<cfoutput group="FileName">
<a href="/cep/cfcontent_file.cfm?Attachment=#FileName#&FileType=#FileExtension#">#FileLinkText#</a>
(#FileExtension#, #FileSize# bytes)<br />
<!--- /output of query filename --->
</cfoutput>
</p>
</div>
<!--- /set padding --->
</div>
<!--- /set bgcolor --->
<!--- /output group on DocumentID --->
</cfoutput>
<!--- /output from query getDocumentsandFiles --->
</cfoutput>
<!--- query getDocumentsandFiles: select columns from tables tbl_CEP_Documents D, tbl_CEP_Document_Topic T, tbl_Document_Has_Topic H, tbl_CEP_files F --->
<!--- to display CEP Document Title, Author, Abstract, Publication Date; and CEP Files associated with those documents; and download links to those files --->
<cfquery name="getDocumentsandFiles" datasource="#ds#">
SELECT D.DocumentID
, D.DocumentTitle
, D.DocumentType
, D.DocumentAuthor
, D.DocumentAbstract
, D.DocumentPublicationDate
, T.DocumentTopicID
, T.DocumentTopic
, H.DocumentID
, H.DocumentTopicID
, F.DocumentID
, F.FileID
, F.FileName
, F.FileLinkText
, F.FileExtension
, F.FileType
, F.FileSize
, F.IsPressRelease
FROM tbl_CEP_Documents D, tbl_CEP_Document_Topic T, tbl_Document_Has_Topic H, tbl_CEP_files F
WHERE D.DocumentID = H.DocumentID
AND D.DocumentID = F.DocumentID
AND F.IsPressRelease = 1
ORDER BY D.DocumentPublicationDate DESC, D.DocumentTitle, D.DocumentID, F.FileName
</cfquery>
<h1>Press Releases</h1>
<!--- begin output from query getDocumentsandFiles; list Document Title, Publication Date; and CEP 'Press Release' Files associated with those documents; and download links to those files --->
<cfoutput query="getDocumentsandFiles" group="DocumentTitle">
<cfif getDocumentsandFiles.currentRow MOD 2 IS 1>
<cfset bgcolor="##ffffff">
<cfelse>
<cfset bgcolor="##f7f5f5">
</cfif>
<!--- set bgcolor --->
<div style="background-color:#bgcolor#;padding:5px;">
<h3>#DocumentTitle#</h3>
<p class="documentText">
<em>Published:</em> <span class="black">#DateFormat(DocumentPublicationDate, "mmmm d, yyyy")#</span><br />
<!--- output of query filename --->
<cfoutput group="FileName">
<a href="/cep/cfcontent_file.cfm?Attachment=#FileName#&FileType=#FileExtension#">#FileLinkText#</a>
(#FileExtension#, #FileSize# bytes)<br />
</cfoutput> <!--- /output of query filename --->
</p>
</div> <!--- /set bgcolor and padding --->
<!--- /output from query getDocumentsandFiles --->
</cfoutput>
<h1>Press Releases</h1>
<!--- begin output from query getDocumentsandFiles; list Document Title, Publication Date; and CEP 'Press Release' Files associated with those documents; and download links to those files --->
<cfoutput query="getDocumentsandFiles" group="DocumentTitle">
<cfif CurrentRow MOD 2 IS 1>
<cfset bgcolor="##ffffff">
<cfelse>
<cfset bgcolor="##f7f5f5">
</cfif>
<!--- set set bgcolor and padding --->
<div style="background-color:#bgcolor#;padding:5px;">
<h3>#getDocumentsandFiles.DocumentTitle#</h3>
<p class="documentText">
<em>Published:</em> <span class="black">#DateFormat(getDocumentsandFiles.DocumentPublicationDate, "mmmm d, yyyy")#</span><br />
<!--- output of query filename --->
<cfoutput group="FileName">
<a href="/cep/cfcontent_file.cfm?Attachment=#FileName#&FileType=#FileExtension#">#getDocumentsandFiles.FileLinkText#</a>
(#getDocumentsandFiles.FileExtension#, #getDocumentsandFiles.FileSize# bytes)<br />
</cfoutput>
<!--- /output of query filename --->
</p>
</div>
<!--- /set bgcolor and padding --->
<!--- /output of query getDocumentsandFiles --->
</cfoutput>
<h1>Press Releases</h1>
<!--- begin output from query getDocumentsandFiles; list Document Title, Publication Date; and CEP 'Press Release' Files associated with those documents; and download links to those files --->
<cfoutput query="getDocumentsandFiles" group="DocumentTitle">
<cfif getDocumentsandFiles.CurrentRow MOD 2 eq 0>
<cfset bgcolor="##ffffff">
<cfelse>
<cfset bgcolor="##f7f5f5">
</cfif>
<!--- set set bgcolor and padding --->
<div style="background-color:#bgcolor#;padding:5px;">
<h3>#getDocumentsandFiles.DocumentTitle#</h3>
<p class="documentText">
<em>Published:</em> <span class="black">#DateFormat(getDocumentsandFiles.DocumentPublicationDate, "mmmm d, yyyy")#</span><br />
<!--- output of query filename --->
<cfoutput>
<a href="/cep/cfcontent_file.cfm?Attachment=#FileName#&FileType=#FileExtension#">#getDocumentsandFiles.FileLinkText#</a>
(#getDocumentsandFiles.FileExtension#, #getDocumentsandFiles.FileSize# bytes)<br />
</cfoutput>
<!--- /output of query filename --->
</p>
</div>
<!--- /set bgcolor and padding --->
<!--- /output of query getDocumentsandFiles --->
</cfoutput>
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
From novice to tech pro — start learning today.
FROM tbl_CEP_Documents D
, tbl_CEP_Document_Topic T
, tbl_Document_Has_Topic H
, tbl_CEP_files F
WHERE D.DocumentID = H.DocumentID
AND D.DocumentID = F.DocumentID
AND F.IsPressRelease = 1
ORDER BY D.DocumentPublicationDate DESC, D.DocumentTitle, D.DocumentID, F.FileName
Looking at this, I see the _Topic table included in the from clause, but there are no join conditions for this table (nothing in the where clause).
Btw, why aren't you using the standard format for joining?
FROM tbl_CEP_Documents D
inner join tbl_CEP_Document_Topic T on t. ??????
inner join tbl_Document_Has_Topic H on D.DocumentID = H.DocumentID
left join tbl_CEP_files F on D.DocumentID = F.DocumentID and F.IsPressRelease = 1
ORDER BY D.DocumentPublicationDate DESC
, D.DocumentTitle
, D.DocumentID
, F.FileName