> 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?
>
Well, I think 5 is right. When a.id=5, the first 'left join' matches the
last record in the 'b' table. Now b.idB=4. This matches the forth record
in a2. a2.tipo='C' and your case returns 0 when a2.tipo is not 'M' or 'F'.
As for row 2, I'm not so sure. No record in the second table has b.idA
value of 2 so the 'left join b on (a.id=b.idA)' doesn't match any records.
But your case on a.tipo should fall to the 'when 'M' then a.valor/2'
expression and be able to return 6.4. The fact that it doesn't may mean
some of the other rows are exhibiting the same behavior, but you can't tell
because the value of 'valor' in rows 3 and 4 are 0 anyway.
Anyone have an idea why row 2 is not returning 6.4???
Mike Fochtman
---------------------------------------------------------------------
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
