Is this possible in MySQL?:
SELECT
parent.id,
parent.someColumn,
(SELECT count(*) FROM child_table_1 as c1 WHERE c1.parent_id =
parent.id) as cnt1,
(SELECT count(*) FROM child_table_2 as c2 WHERE c2.parent_id =
parent.id) as cnt2
FROM parent
Not sure if this would give yo
On Jan 4, 2011, at 4:50 PM, Dan Cech wrote:
> Rob,
>
> This is demonstrating 2 special cases which will work properly:
>
> 1. no rows for a parent in one of the child tables
> 2. 1 row for a parent in both child tables
>
> If you run your child inserts again to create more rows you'll end up wit
Rob,
On 1/4/2011 4:28 PM, Rob Marscher wrote:
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
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
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_ta
On Tue, Jan 4, 2011 at 3:57 PM, Dan Cech wrote:
> On 1/4/2011 3:56 PM, David Mintz wrote:
>
>> Guess what. The logic of my SQL must be wrong. I finally let it think for
>> nearly 12 minutes and the results were f***ed up.
>>
>
> .. snip
>
>
> all those count() values that are the same in both co
On 1/4/2011 3:56 PM, David Mintz wrote:
Guess what. The logic of my SQL must be wrong. I finally let it think for
nearly 12 minutes and the results were f***ed up.
.. snip
all those count() values that are the same in both columns are way way too
large. mysql does not understand what I mean.
On Tue, Jan 4, 2011 at 3:44 PM, David Mintz wrote:
>
>
> On Tue, Jan 4, 2011 at 1:33 PM, Rob Marscher
> wrote:
>
>> Looks like you need an index on events.event_type_id
>> and requests.event_type_id. I'm not seeing those in your indexes. I see
>> event_type_id is part of the uniqueRequest inde
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 = pa
> On Jan 4, 2011, at 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_
On Tue, Jan 4, 2011 at 1:33 PM, Rob Marscher wrote:
> Looks like you need an index on events.event_type_id
> and requests.event_type_id. I'm not seeing those in your indexes. I see
> event_type_id is part of the uniqueRequest index, but it can't use it unless
> it's the first column in the index
Looks like you need an index on events.event_type_id and
requests.event_type_id. I'm not seeing those in your indexes. I see
event_type_id is part of the uniqueRequest index, but it can't use it unless
it's the first column in the index or you specify the columns that come before
it in your i
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
and it is so incredibly slow that
13 matches
Mail list logo
