Roman, try to execute this query:
select e.cid,d.amount,dd.amount 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) and after that try to group by them manualy. What do you have as result? Best regards, Mikhail. ----- Original Message ----- From: "Roman Menshikov" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, September 04, 2002 9:47 AM Subject: error in left join > Hello mysql team, > > Server info: > > Release: mysql-3.23.52 (Official MySQL RPM) > Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.52, for pc-linux-gnu on i686 > Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB > This software comes with ABSOLUTELY NO WARRANTY. This is free software, > and you are welcome to modify and redistribute it under the GPL license > Server version 3.23.52 > Protocol version 10 > Connection Localhost via UNIX socket > UNIX socket /var/lib/mysql/mysql.sock > Uptime: 1 day 14 hours 59 min 28 sec > Threads: 1 Questions: 430690 Slow queries: 11 Opens: 58 Flush tables: 1 Open tables: 38 Queries per second avg: 3.068 > 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 > Architecture: i686 > Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc > > Let's say I have 2 tables: > > create table eee(cid int not null primary key, l varchar(10)); > create table ddd(cid int not null, c int not null, amount int); > > And some data: > insert into eee values(1,'1'); > insert into eee values(2,'2'); > insert into eee values(3,'3'); > insert into eee values(4,'4'); > insert into eee values(5,'5'); > insert into ddd values(2,1,10); > insert into ddd values(2,2,100); > insert into ddd values(2,3,1020); > insert into ddd values(4,1,1020); > insert into ddd values(4,2,1020); > insert into ddd values(4,3,1020); > > The problem is: when I issue the query: > select e.cid,sum(d.amount),sum(dd.amount) > 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 > group by e.cid; > > I've got: > +-----+---------------+----------------+ > | cid | sum(d.amount) | sum(dd.amount) | > +-----+---------------+----------------+ > | 1 | 0 | 0 | > | 2 | 1120 | 2040 | > | 3 | 0 | 0 | > | 4 | 2040 | 2040 | > | 5 | 0 | 0 | > +-----+---------------+----------------+ > > while I shuld get quite other result: > +-----+---------------+----------------+ > | cid | sum(d.amount) | sum(dd.amount) | > +-----+---------------+----------------+ > | 1 | 0 | 0 | > | 2 | 1120 | 1020 | > | 3 | 0 | 0 | > | 4 | 2040 | 1020 | > | 5 | 0 | 0 | > +-----+---------------+----------------+ > > See the difference in the 3rd columns??? > -- > Best regards, > Roman mailto:[EMAIL PROTECTED] > > > > --------------------------------------------------------------------- > 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 > --------------------------------------------------------------------- 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
