Roman,
Wednesday, September 04, 2002, 10:47:18 AM, you wrote:
RM> Server info:
RM> Release: mysql-3.23.52 (Official MySQL RPM)
RM> Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.52, for pc-linux-gnu on i686
RM> Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
RM> This software comes with ABSOLUTELY NO WARRANTY. This is free software,
RM> and you are welcome to modify and redistribute it under the GPL license
RM> Server version 3.23.52
RM> Protocol version 10
RM> Connection Localhost via UNIX socket
RM> UNIX socket /var/lib/mysql/mysql.sock
RM> Uptime: 1 day 14 hours 59 min 28 sec
RM> Threads: 1 Questions: 430690 Slow queries: 11 Opens: 58 Flush tables: 1 Open
tables: 38 Queries per second avg: 3.068
RM> System: Linux ic-tm-s-slave-0001.intercaf.ru 2.4.7-10enterprise #1 SMP Thu Sep 6
16:48:20 EDT 2001 i686 unknown
RM> Architecture: i686
RM> Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
RM> Let's say I have 2 tables:
RM> create table eee(cid int not null primary key, l varchar(10));
RM> create table ddd(cid int not null, c int not null, amount int);
RM> And some data:
RM> insert into eee values(1,'1');
RM> insert into eee values(2,'2');
RM> insert into eee values(3,'3');
RM> insert into eee values(4,'4');
RM> insert into eee values(5,'5');
RM> insert into ddd values(2,1,10);
RM> insert into ddd values(2,2,100);
RM> insert into ddd values(2,3,1020);
RM> insert into ddd values(4,1,1020);
RM> insert into ddd values(4,2,1020);
RM> insert into ddd values(4,3,1020);
RM> The problem is: when I issue the query:
RM> select e.cid,sum(d.amount),sum(dd.amount)
RM> from eee e
RM> left join ddd d on d.cid=e.cid and d.c>=2 and d.c<=3
RM> left join ddd dd on dd.cid=e.cid and dd.c=3
RM> group by e.cid;
RM> I've got:
RM> +-----+---------------+----------------+
RM> | cid | sum(d.amount) | sum(dd.amount) |
RM> +-----+---------------+----------------+
RM> | 1 | 0 | 0 |
RM> | 2 | 1120 | 2040 |
RM> | 3 | 0 | 0 |
RM> | 4 | 2040 | 2040 |
RM> | 5 | 0 | 0 |
RM> +-----+---------------+----------------+
RM> while I shuld get quite other result:
RM> +-----+---------------+----------------+
RM> | cid | sum(d.amount) | sum(dd.amount) |
RM> +-----+---------------+----------------+
RM> | 1 | 0 | 0 |
RM> | 2 | 1120 | 1020 |
RM> | 3 | 0 | 0 |
RM> | 4 | 2040 | 1020 |
RM> | 5 | 0 | 0 |
RM> +-----+---------------+----------------+
RM> See the difference in the 3rd columns???
It shouldn't be.
Look:
mysql> select *
-> from eee e
-> left join ddd d on d.cid=e.cid and d.c>=2 and d.c<=3
-> left join ddd dd on dd.cid=e.cid and dd.c=3;
+-----+------+------+------+--------+------+------+--------+
| cid | l | cid | c | amount | cid | c | amount |
+-----+------+------+------+--------+------+------+--------+
| 1 | 1 | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | 2 | 2 | 2 | 100 | 2 | 3 | 1020 |
| 2 | 2 | 2 | 3 | 1020 | 2 | 3 | 1020 |
| 3 | 3 | NULL | NULL | NULL | NULL | NULL | NULL |
| 4 | 4 | 4 | 2 | 1020 | 4 | 3 | 1020 |
| 4 | 4 | 4 | 3 | 1020 | 4 | 3 | 1020 |
| 5 | 5 | NULL | NULL | NULL | NULL | NULL | NULL |
+-----+------+------+------+--------+------+------+--------+
7 rows in set (0.01 sec)
If you group and sum d.amount and dd.amount, what result do you get?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
---------------------------------------------------------------------
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
