[rt-users] Query on Ticket-Transactions Custom Fields --Solved

[Carlos Ramon Lopez Midence]

Here is the query that displays the values of custom fields on Ticket-Transactions, It took me a while to get it but here it is for the list:
 
 
SELECTT.Id, OCFV.ObjectId, Tr.ObjectId,Tr.Type,T.queue,T.subject,T.Status,T.Resolved,CF.Name,OCFV.Content  FROM ((Tickets T LEFT JOIN Transactions TR on ((Tr.objectid = t.id))LEft join ObjectCustomFieldValues OCFV on ((OCFV.Objectid = tr.id))left join Customfields CF on ((CF.id = OCFV.CustomField))))WHERE T.Status = 'Resolved'and OCFV.objecttype = 'RT::TRansaction' and TR.type = 'Comment' and T.id = '1127'
 
One thing to notice is the left join on OCFV.ObjectId is equal to Transactions.ID (There seem to be a confusion when it comes to Transactions.ObjectID and ObjectCustomFieldValues.ObjectID: They are not the same, nor they point to each other)
 
Suggestions on how to improve this query are more than welcome....
 
Carlos
 
 
_________________________________________________________________
Hotmail to go? Get your Hotmail, news, sports and much more! Check out the New MSN Mobile! 
http://mobile.msn.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20070702/3b56e884/attachment.htm>