NYCPHP Meetup

[Dan Cech]

On 1/4/2011 1:05 PM, David Mintz wrote:
> I am trying to do something like this:
>
> SELECT parent.id, parent.someColumn, count(child_table_1.id), count(
> child_table_2.id) FROM parent
> LEFT JOIN child_table_1 ON child_table_1.parent_id = parent.id
> LEFT JOIN child_table_2 ON child_table_2.parent_id = parent.id

The problem is the multiple counts from different child tables, you're 
ending up with an enormous number of rows because you have every row in 
child_table_2 duplicated in the result for each row in child_table_1.

There isn't a really nice way to answer this question without resorting 
to multiple queries, but one approach goes something like:

SELECT parent.id, parent.someColumn, c1.cnt, c2.cnt
FROM parent
LEFT JOIN (
	SELECT child_table_1.parent_id,count(child_table_1.id) as cnt
	FROM child_table_1
	GROUP BY child_table_1.parent_id
) AS c1
LEFT JOIN (
	SELECT child_table_2.parent_id,count(child_table_2.id) as cnt
	FROM child_table_2
	GROUP BY child_table_2.parent_id
) AS c2

Have fun!

Dan