I am having a or problem
reproducible by script below
(or by sql or by query if
mail filter likes those words
better:) ).
Briefly:
4 tables joined to each other:
/-ar
a
\-b-br
I would expect to get totals based on
the root record in a:
a_id q_a q_ar q_b q_br
1 1 4 12 44
But I am getting:
a_id q_a q_ar q_b q_br
1 8 16 44 88
It obviously multiplies each total
by number of records processed in the
later joins.
Is this the right behavior?
Is it a way to get the expected
behavior which is quite natural?
Why does it count each record in ar
again once it finds new records for later
joins?
Thanx,
Yuri.
-------cut----script begins------------
create temporary table a(a_id int, q int);
create temporary table ar(ar_id int, q int, a_id int);
create temporary table b(b_id int, q int, a_id int);
create temporary table br(br_id int, q int, b_id int);
insert into a values(1, 1);
insert into ar values(1, 1, 1);
insert into ar values(2, 3, 1);
insert into b values(1, 5, 1);
insert into b values(2, 7, 1);
insert into br values(1, 5, 1);
insert into br values(2, 11, 1);
insert into br values(3, 11, 1);
insert into br values(4, 17, 2);
select
a.a_id,
sum(a.q) as q_a,
sum(ar.q) as q_ar,
sum(b.q) as q_b,
sum(br.q) as q_br
from
a
left join ar on (ar.a_id = a.a_id)
left join b on (b.a_id = a.a_id)
left join br on (br.b_id = b.b_id)
group by a.a_id
-------cut----script ends------------
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
