This content has been marked as final. Show 2 replies
Considering that Q of Q does not support left joins, could you show us the one you wrote that does not get an error?
i was trying to keep the post simple..
the left outer join is in the original MS SQL query.. (which caches the data in an application variable.)
Then on the request, I'm doing a Q of Q to futher sort and select the data. I wrote a workaround that loops through the "grouped" master records and updates the Count field. (with only distinct childs)
<!--- this is how i need to present the data --->
<cfquery name="getlistqry" dbtype="query">
SELECT Count(my_Key) AS my_Count, SUM(my_Amount) AS my_Amount_Sum, master_Key ...
GROUP BY master_Key ...
ORDER BY #orderby# #direction#, #orderby2#
<!--- this updates the count field to change the 1 to 0 for master records without a child --->
<cfquery name="tempqry" dbtype="query">
SELECT * FROM countqry WHERE child_master_key = #getlistqry.master_Key#
This updates the count with actual counts of the child records. (ie. it changes the number 1 to a 0 whenever there is NO child.)
It's not pretty, seems like it could be done in the first Q of Q select. But it gets the job done and probably still a lot faster than trying to do a large MS SQL query at run time.