If you supress aggregate funtions and "group by" clause then:
select *,
(case a.tipo
when 'F' then
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);
And the result is:
+----+------+-------+------+------+----------+------+------+-------+----
------------+
| id | tipo | valor | idA | idB | cantidad | id | tipo | valor |
valorCalculado |
+----+------+-------+------+------+----------+------+------+-------+----
------------+
| 1 | F | 10.3 | 1 | 2 | 1 | 2 | M | 12.8 |
6.4 |
| 1 | F | 10.3 | 1 | 3 | 2 | 3 | C | 0 |
0 |
| 2 | M | 12.8 | NULL | NULL | NULL | NULL | NULL | NULL |
6.4 |
| 3 | C | 0 | NULL | NULL | NULL | NULL | NULL | NULL |
NULL |
| 4 | C | 0 | NULL | NULL | NULL | NULL | NULL | NULL |
NULL |
| 5 | M | 11.2 | 5 | 4 | 1 | 4 | C | 0 |
5.6 |
| 6 | F | 12.3 | NULL | NULL | NULL | NULL | NULL | NULL |
0 |
+----+------+-------+------+------+----------+------+------+-------+----
------------+
7 rows in set (0.01 sec)
And with "Group By" clause:
select *,
(case a.tipo
when 'F' then
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;
The result is:
+----+------+-------+------+------+----------+------+------+-------+----
------------+
| id | tipo | valor | idA | idB | cantidad | id | tipo | valor |
valorCalculado |
+----+------+-------+------+------+----------+------+------+-------+----
------------+
| 1 | F | 10.3 | 1 | 2 | 1 | 2 | M | 12.8 |
6.4 |
| 2 | M | 12.8 | NULL | NULL | NULL | NULL | NULL | NULL |
6.4 |
| 3 | C | 0 | NULL | NULL | NULL | NULL | NULL | NULL |
NULL |
| 4 | C | 0 | NULL | NULL | NULL | NULL | NULL | NULL |
NULL |
| 5 | M | 11.2 | 5 | 4 | 1 | 4 | C | 0 |
5.6 |
| 6 | F | 12.3 | NULL | NULL | NULL | NULL | NULL | NULL |
0 |
+----+------+-------+------+------+----------+------+------+-------+----
------------+
It isn't wrong.
But with "SUM" function...
Jose Ceferino Ortega
-----Mensaje original-----
De: Fochtman, Michael [mailto:[EMAIL PROTECTED]]
Enviado el: viernes, 01 de febrero de 2002 14:19
Para: 'J. Ceferino Ortega'; [EMAIL PROTECTED]
Asunto: RE: What is wrong?
> 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
