I have 2 tables:
CREATE TABLE `a` (
`id` varchar(12) NOT NULL default '',
`tipo` char(1) default NULL,
`valor` double default NULL,
PRIMARY KEY (`id`)
) TYPE=InnoDB;
CREATE TABLE `b` (
`idA` varchar(12) NOT NULL default '',
`idB` varchar(12) NOT NULL default '',
`cantidad` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`idA`,`idB`)
) TYPE=InnoDB;
And these datas:
INSERT INTO `a`
VALUES("1","F","10.3"),("2","M","12.8"),("3","C","0"),("4","C","0"),("5"
,"M","11.2"),("6","F","12.3");
INSERT INTO `b` VALUES("1","2","1"),("1","3","2"),("5","4","1");
When I do this query:
select
a.id,
a.tipo,
(case a.tipo
when 'F' then
sum(
case a2.tipo
when 'F' then a2.valor
when 'M' then a2.valor/2
else 0
end
)
when 'M' then a.valor/2
else null
end) as valorCalculado
from a
left join b on (a.id=b.idA)
left join a a2 on (b.idB=a2.id)
group by a.id;
Result is:
+----+------+----------------+
| id | tipo | valorCalculado |
+----+------+----------------+
| 1 | F | 6.4 |
| 2 | M | 0 |
| 3 | C | 0 |
| 4 | C | 0 |
| 5 | M | 0 |
| 6 | F | 0 |
+----+------+----------------+
Rows 2 and 5 are wrong. Why?
Thanks,
Jos� Ceferino Ortega
---------------------------------------------------------------------
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