I have a query in which I need to display some group function figures SUM COUNT etc from a joined table. I also need to select the latest version (by datetime) of a matching record from a separate table, which I can do using an ordered subquery in the join.
My issue is that the group functions appear to be joining on every row in the subquery (not just the latest one) and thus displaying incorrect inflated results of the SUM/COUNT functions.
So I guess what I'm looking for is one of the following:
1) a better way to do the group functions so that the joins don't affect them
2) a way to select only the single latest row in my subquery so the extraneous rows aren't included in the join
3) a different approach to the joins themselves that aleviates the problem.
Here is a simplified version of the query, any ideas greatly appreciated.
SELECT p.field1, p.field2, p.field3, SUM(pp.amount1), SUM(pp.amount2), COUNT(c.field1), SUM(c.field1), s.Status, l.lookupvalues FROM tableP p LEFT JOIN tableP pp ON pp.field1 = p.field1 AND pp.field2 = p.field2 AND pp.field3=0 LEFT JOIN someotherlookuptables l on l.id=p.lookupid LEFT JOIN tableC c ON c.id = p.cid LEFT JOIN (SELECT * FROM status ORDER BY dateChanged DESC) s ON s.pid = p.id GROUP BY p.field1
I should add that I have narrowed it down to eliminate other LEFT JOINed tables in the query, which I have tested and have no affect on the group functions.
Also, if I alter the subquery to return only the first row, ie:
(SELECT * FROM stats ORDER BY dateChanged DESC LIMIT 1) the group functions return correct values, but obviously only one record is displayed with a Status.
Edited by |-|x: additional information