Here is my query:
<cfquery name="getUserTickets" datasource="#request.dsn#"
username="#request.dbuser#" password="#request.dbpass#">
SELECT MAX(Th.dateReceived) AS dateReceived, T.ticketID, Q.queueID,
Q.queueName, TS.statusName
FROM ((Tickets T
LEFT JOIN Queues Q
ON T.queue = Q.queueID)
LEFT JOIN ticketStatus TS
ON T.status = TS.statusID)
LEFT JOIN threads Th
ON T.ticketID = Th.ticketID
WHERE T.ticketID >= 1 AND
T.ticketID <= 400 AND
T.owner <> 0 AND
(TS.statusName = 'New' OR TS.statusName = 'Open')
ORDER BY T.priority desc, T.ticketID
</cfquery>
This works as is without the MAX(Th.dateReceived) select. When I include
that I get the dreaded "Column 'Q.queueID' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause." If I include all the other fields in the GROUP BY clause, I get:
"The text, ntext, and image data types cannot be compared or sorted, except
when using IS NULL or LIKE operator."
The field in question is the T.Subject field. Is there anyway of performing
this query? Any help is appreciated.
Cedric
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
