NYCPHP Meetup

[Rob Marscher]

On Jan 4, 2011, at 4:02 PM, Dan Cech wrote:

> Sorry, forgot the ON clauses:
> 
> 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 ON c1.parent_id=parent.id
> 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 ON c2.parent_id=parent.id

Yeah, breaking it into subqueries like this does make it a bit more clear about what's going on.  For what it's worth, David's query worked in a little test I did.  Not sure why it produces the wrong results for him... but maybe due to other things going on in the query, subqueries are necessary to get the correct totals.

create table parent (
	id int unsigned not null auto_increment,
 	name varchar(30) not null default '',
	primary key (id)
);
create table child1 (
	id int unsigned not null auto_increment,
	parent_id int unsigned not null,
	primary key (id),
	index idx_parent (parent_id)
);
create table child2 (
	id int unsigned not null auto_increment,
	parent_id int unsigned not null,
	primary key (id),
	index idx_parent (parent_id)
);
insert into parent (name) values ('One'),('Two'),('Three');
insert into child1 (parent_id) values (1),(1),(1),(3);
insert into child2 (parent_id) values (2),(2),(2),(3);

explain select parent.id, parent.name, count(child1.id), count(child2.id) 
from parent force index (primary) 
left join child1 on parent.id = child1.parent_id 
left join child2 on parent.id = child2.parent_id
group by parent.id;

+----+-------------+--------+-------+---------------+---------+---------+--------------------------+------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref                      | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+--------------------------+------+-------+
|  1 | SIMPLE      | parent | index | NULL          | PRIMARY | 4       | NULL                     |    1 |       |
|  1 | SIMPLE      | child1 | ref   | parent        | parent  | 4       | brightbu_namco.parent.id |    2 |       |
|  1 | SIMPLE      | child2 | ref   | parent        | parent  | 4       | brightbu_namco.parent.id |    2 |       |
+----+-------------+--------+-------+---------------+---------+---------+--------------------------+------+-------+
+----+-------+------------------+------------------+
| id | name  | count(child1.id) | count(child2.id) |
+----+-------+------------------+------------------+
|  1 | One   |                3 |                0 |
|  2 | Two   |                0 |                3 |
|  3 | Three |                1 |                1 |
+----+-------+------------------+------------------+

explain select parent.id, parent.name, c1.cnt, c2.cnt 
from parent force index (primary)
left join (
	select parent_id, count(0) as cnt from child1 group by parent_id) c1 
	on c1.parent_id = parent.id 
left join (
	select parent_id, count(0) as cnt from child2 group by parent_id) c2 
	on c2.parent_id = parent.id
group by parent.id;
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY     | parent     | index | NULL          | PRIMARY | 4       | NULL |    1 |             |
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    2 |             |
|  1 | PRIMARY     | <derived3> | ALL   | NULL          | NULL    | NULL    | NULL |    2 |             |
|  3 | DERIVED     | child2     | index | NULL          | parent  | 4       | NULL |    4 | Using index |
|  2 | DERIVED     | child1     | index | NULL          | parent  | 4       | NULL |    4 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
+----+-------+------+------+
| id | name  | cnt  | cnt  |
+----+-------+------+------+
|  1 | One   |    3 | NULL |
|  2 | Two   | NULL |    3 |
|  3 | Three |    1 |    1 |
+----+-------+------+------+




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20110104/4d6a9b34/attachment.html>