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
_______________________________________________
New York PHP Users Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
http://www.nyphp.org/Show-Participation
