Oops. I should have reread this before sending.
I don't know about the expert part, but what I would do is break it down
into two
queries. The first one gets the min(m.MessageDateCreated), and the second
would pull all the rest.
<cfquery datasource="#DSN#" name="query1" blockfactor="100">
SELECT m.MessageID, Min(m.messagedatecreated)
FROM Messages m
WHERE m.ThreadID = #attributes.ThreadID#
GROUP BY m.MessageID, m.messagedatecreated
</cfquery>
In the second query, you would basically do:
<cfoutput query="query1">
<cfquery datasource="#DSN#" name="query2" blockfactor="100">
SELECT Fieldnames
FROM Messages m
WHERE m.ThreadID = #attributes.ThreadID#
AND NOT m.MessageID IN (#ValueList(firstquery.messageID)#)
</cfquery>
</cfoutput>
I can't remember off the top of my head whether the NOT goes before or after
the m.MessageID. But basically with the first query, you would get a
valuelist of the messageID, then in the second you would pull all records
except the record found in the first query.
Of course, this may not be what you are looking for, but it is an option.
David Henry
HPER Technology Services
School of Health, Physical Education and Recreation
Indiana University
(812)855-4270
[EMAIL PROTECTED]
-----Original Message-----
From: Henry, David B [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 24, 2000 8:40 AM
To: Fusebox
Subject: RE: ... should be a simple query question ...
I don't know about the expert part, but what I would do is break it down
into two
queries. The first one gets the min(m.MessageDateCreated), and the second
would pull all the rest. In the second query, you would basically do
QUERY 1
SELECT m.MessageID, Min(m.messagedatecreated)
FROM Messages m
WHERE m.ThreadID = #attributes.ThreadID#
GROUP BY m.MessageID, m.messagedatecreated
<cfoutput query="query1">
<cfquery datasource="#DSN#" name="query2" blockfactor="100">
SELECT Fieldnames
FROM Messages m
WHERE m.ThreadID = #attributes.ThreadID#
AND NOT m.MessageID IN (#ValueList(firstquery.messageID)#)
</cfquery>
</cfoutput>
I can't remember off the top of my head whether the NOT goes before or after
the m.MessageID. But basically with the first query, you would get a
valuelist of the messageID, then in the second you would pull all records
except the record found in the first query.
Of course, this may not be what you are looking for, but it is an option.
HTH
David Henry
HPER Technology Services
School of Health, Physical Education, & Recreation
Indiana University
(812) 855-4270
[EMAIL PROTECTED]
-----Original Message-----
From: Russell Jones [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 23, 2000 9:38 PM
To: Fusebox
Subject: ... should be a simple query question ...
First off, I'm using Access 2000...
I need this query to get all messages in a thread except the first message
that started it. What I have clearly does not work. I seems like it should,
but it doesn't.
==============
<cfquery name="request.getMessageTaxonomy" datasource="#request.userdsn#"
cachedwithin="#CreateTimeSpan(0,0,IIF(Flush,0,QueryCacheTime),0)#">
SELECT m.*, m.MessageID AS ItemID, m.MessageParent AS ParentItemID
FROM Messages m
WHERE m.ThreadID = #attributes.ThreadID#
AND m.MessageDateCreated >= ALL
(
SELECT Min(m.MessageDateCreated)
FROM Messages m
WHERE m.ThreadID = #attributes.ThreadID#
)
ORDER BY m.MessageDateCreated ASC
</cfquery>
==============
I know I could cut out the first record with CF when I output it, but I'd
like to do it in the query because I know it can be done.
Are there any giving experts out there to answer my cry for help?
Thanks.
-Russ
----------------------------------------------------------------------------
--
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/fusebox or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
----------------------------------------------------------------------------
--
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/fusebox or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/fusebox or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
