Hey, Im trying to do a left join from 2 tables and, at the same time, trying to get the average of a coulmn in the 2nd table used in the left join. How can I do this?
You can get the average of the rows satisfying the ON and WHERE clause conditions. The fact that there is a join involved shouldn't matter (although you'll have to watch for NULLs from the left join). If you want the average to also consider rows not selected you could do it with a subselect, but not in mysql since it doesn't support subselects. You would need a second query for that.
SELECT table1.field, avg(table2.field) FROM table1 LEFT JOIN table2 ON table1.this=table2.that GROUP BY table1.field;
Note: when using a 'group by' you can only select aggregates (min,max,avg etc) and fields that are mentioned in the GROUP BY.
