dúvida com um JOIN

Assuntos Gerais
#1

galera, criei umas tabelas de teste populadas assim:

[code]teste_pedido
id | id_despesa | total
----±-----------±------
1 | 1 | 100
2 | 1 | 200
3 | 1 | 300
4 | 2 | 400
5 | 2 | 500

teste_romaneio
id | id_despesa | total
----±-----------±------
1 | 1 | 1000
2 | 1 | 2000
3 | 2 | 3000

teste_despesa
id | nome
----±----------
1 | despesa 1
2 | despesa 2
3 | despesa 3[/code]

e preciso de um select que me retorne as somas dos totais dos pedidos e dos romaneios por despesas…
fazendo cada um separado ficou assim:

teste=# SELECT despesa.nome AS nome, COALESCE(SUM(pedido.total),0) as executado_pedido teste=# FROM teste_despesa AS despesa teste=# LEFT JOIN teste_pedido AS pedido ON pedido.id_despesa = despesa.id teste=# GROUP BY despesa.nome ORDER BY despesa.nome; nome | executado_pedido -----------+------------------ despesa 1 | 600 despesa 2 | 900 despesa 3 | 0 (3 rows)
e

teste=# SELECT despesa.nome AS nome, COALESCE(SUM(romaneio.total),0) as executado_romaneio teste=# FROM teste_despesa AS despesa teste=# LEFT JOIN teste_romaneio AS romaneio ON romaneio.id_despesa = despesa.id teste=# GROUP BY despesa.nome ORDER BY despesa.nome; nome | executado_romaneio -----------+-------------------- despesa 1 | 3000 despesa 2 | 3000 despesa 3 | 0 (3 rows)
tudo certinho… só que tentando juntar num query só eu esbarrei num problema:

teste=# SELECT despesa.nome AS nome, COALESCE(SUM(pedido.total),0) as executado_pedido, COALESCE(SUM(romaneio.total),0) as executado_romaneio teste=# FROM teste_despesa AS despesa teste=# LEFT JOIN teste_pedido AS pedido ON pedido.id_despesa = despesa.id teste=# LEFT JOIN teste_romaneio AS romaneio ON romaneio.id_despesa = despesa.id teste=# GROUP BY despesa.nome ORDER BY despesa.nome; nome | executado_pedido | executado_romaneio -----------+------------------+-------------------- despesa 1 | 1200 | 9000 despesa 2 | 900 | 6000 despesa 3 | 0 | 0 (3 rows)
juntando com 2 “JOINs” os valores saem errados (os totais de romaneios multiplicados pelo numero de pedidos e vice-versa) como faço pra acabar com essa “multiplicação matricial” aí?
valeu!

#2

Estou sem um cliente sql aqui, mas tente o seguinte:

SELECT *
FROM
  teste_despesa despesa

  LEFT JOIN 

    (SELECT id_despesa, SUM(total) total FROM teste_pedido GROUP BY id_despesa) pedido

  ON despesa.id_despesa = pedido.id_despesa

  LEFT JOIN

    (SELECT id_despesa, SUM(total) total FROM teste_romaneio GROUP BY id_despesa) romaneio
  ON despesa.id_despesa = romaneio.id_despesa

Você soma o total de romaneios e pedidos em subqueries separadas.
Depois faz um left join simples com os resultados dessas subqueries.

#3

tive que corrigir algumas coisinhas aqui, mas entendi a lógica da coisa e fiz o select do jeito que eu precisava aqui:

SELECT cc.nome_reduzido AS nome, occ.valor AS planejado, pedidos.executado_pedido, romaneios.executado_romaneio FROM ordem_centrocustos AS occ INNER JOIN centrocustos AS cc ON cc.id = occ.id_centrocusto AND cc.id_aplicacao = 9 LEFT JOIN ( SELECT cc.id AS id_cc, COALESCE(SUM(p.total),0) as executado_pedido FROM ordem_centrocustos AS occ INNER JOIN centrocustos AS cc ON cc.id = occ.id_centrocusto LEFT JOIN pedidos AS p ON p.id_ordem_centrocusto = occ.id GROUP BY occ.id_ordem, cc.id_aplicacao, cc.id HAVING occ.id_ordem = 2 AND cc.id_aplicacao = 9 ) pedidos ON pedidos.id_cc = cc.id LEFT JOIN ( SELECT cc.id AS id_cc, COALESCE(SUM(r.valor_ordem_centrocusto),0) as executado_romaneio FROM ordem_centrocustos AS occ INNER JOIN centrocustos AS cc ON cc.id = occ.id_centrocusto LEFT JOIN romaneios AS r ON r.id_ordem_centrocusto = occ.id GROUP BY occ.id_ordem, cc.id_aplicacao, cc.id HAVING occ.id_ordem = 2 AND cc.id_aplicacao = 9 ) romaneios ON romaneios.id_cc = cc.id WHERE occ.id_ordem = 2 ORDER BY cc.nome_reduzido

só fica uma dúvida: eu tenho que usar todas as restrições dentro dos subquerys né? pra não ter o problema da multiplicação cruzada de novo…
obrigadão!

#4