Hi,
I'm running CF5 and SQL version 8.0, and have the following queries:
<!--- find applicants for the current issue of GradNews --->
<cfquery name="getApplicants" datasource="#application_dsn#">
SELECT gna.ApplicantID, gna.FirstName, gna.MiddleName, gna.LastName,
gna.Email,
m.Department_Name AS DeptName
FROM tblGradNewsApplicants gna
INNER JOIN tblGradNewsIssues gni ON gna.PublicationID =
gni.PublicationID
INNER JOIN tblMajors m ON gna.DepartmentID = m.Department_ID
WHERE gna.ActiveYN = 1
AND gni.ActiveYN = 1
ORDER BY m.Department_Name, gna.LastName, gna.FirstName
</cfquery>
<cfquery name="qryGetArts" datasource="#application_dsn#">
SELECT ArticleID, title, coauthors, journal, volume, URL,
articledate, articleType
FROM Articles
WHERE DateSubmitted BETWEEN #CreateODBCDate(form.datefrom)# AND
#CreateODBCDate(form.dateto)#
AND ApplicantID = #getApplicants.ApplicantID#
AND articleType = 'art'
GROUP BY ArticleID, title, coauthors, journal, volume, URL,
articledate, articleType
</cfquery>
The DateSubmitted used is today's date. There are 4 records in the
Articles table, all of which have today's date.
However, when I run my web page, I get back only one record from the
2nd query (output below). None of the records for ApplicantID=56 are
being returned.
-----output -----
Articles in Journals, Magazines, or Newspapers
Computer Science
Jennifer blah
"Studey of C60 trans****t in proous media adnasdifsodfijsodifj," with
A. T. Kam, M.B. Tomson, (J Mater, 2012 , 1106 ).
----- end output ------
But, if run the queries in Query Analzyer, I get the correct number of
records from the 2nd query. (queries and output below)
SELECT gna.ApplicantID, gna.FirstName, gna.MiddleName, gna.LastName,
gna.Email,
m.Department_Name AS DeptName
FROM tblGradNewsApplicants gna
INNER JOIN tblGradNewsIssues gni ON gna.PublicationID =
gni.PublicationID
INNER JOIN tblMajors m ON gna.DepartmentID = m.Department_ID
WHERE gna.ActiveYN = 1
AND gni.ActiveYN = 1
ORDER BY m.Department_Name, gna.LastName, gna.FirstName
SELECT ArticleID, title, coauthors, journal, volume, URL, articledate,
articleType
FROM Articles
WHERE DateSubmitted BETWEEN '2007-02-02' AND '2007-02-02'
AND articleType = 'art'
AND applicantid = 56
GROUP BY ArticleID, title, coauthors, journal, volume, URL,
articledate, articleType
---------output ------------
2 <em>An intramolecular t-SNARE comples functions in vivo without the
syntaxin NH-2-terminal regulartory domin</em> Jeffery S. Van, Komen
JCB 172 NULL Jan 2006 art 2007-02-02 00:00:00.000
3 <em>An intrmolcular t-SNARE Jeffery S. Van, Komen JCB 172 NULL Jan
2006 art 2007-02-02 00:00:00.000
4 <em>The polybasic Juxtamembran Van Komen, J.S Eukaryot Cell 12 NULL
Dec 2005 art 2007-02-02 00:00:00.000
---------end output ---------
So, I thought perhaps the CF query was not returning the records for
ApplicantID = 56, so I actually added "AND gna.ApplicantID = 56" to
the 1st query. When I refreshed the page, ONE of the three records
showed up. When I took the AND statement back out, none of them
showed up, just as before.
I've cleared my cache, tested it on different browsers and even a
different computer with the same results, so I think it might be
ColdFusion.
I have no idea why this is happening - can someone please give me some
guidance on this?
THANKS!
|
