What is wrong?

[J. Ceferino Ortega]

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