Re: RV: bad result in a query!! :-(

Hi!

-----Mensaje original-----
De: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]En nombre de Jochem van
Dieten
Enviado el: jueves, 17 de octubre de 2002 16:14
Para: Jose Antonio Leo
CC: pgsql-general
Asunto: Re: [GENERAL] RV: bad result in a query!! :-(

Query rewritten with some indentation:

SELECT
vtdiaaec.cod_ae1,
aecoc.des_ae,

Sum(vtdiaaec.ven_uni) AS
-- You are missing something here, copy-paste error I presume
Sorry, that's.

Sum(vtdiaaec.ven_pco) AS SumaDeven_pco,
Sum(vtdiaaec.ven_siv) AS SumaDeven_siv,
Sum(vtdiaaec.ven_civ) AS SumaDeven_civ,
Sum(vtdiaaec.ven_ofe) AS SumaDeven_ofe,
Sum(vtdiaaec.cos_ofe) AS SumaDecos_ofe
FROM
vtdiaaec LEFT JOIN aecoc ON vtdiaaec.cod_ae1 = aecoc.cod_ae1
WHERE
aecoc.cod_ae2=0
AND aecoc.cod_ae3=0
AND aecoc.cod_ae4=0
AND aecoc.cod_ae5=0
AND extract (year from vtdiaaec.fecha)='2002'
GROUP BY
vtdiaaec.cod_ae1,
aecoc.des_ae
ORDER BY
vtdiaaec.cod_ae1;

> The table aecoc has a primary key (cod_ae1,cod_ae2,cod_ae3) and the
> table vtdiaaec has a key with the fields cod_ae1,cod_ae2,cod_ae3.

>Something I don't understand about this query: why the LEFT JOIN and
>not an INNER JOIN? Isn't it true that each row of the vtdiaaec table
>that does not have a matching row in aecoc table gets included in the
>join result with each field that originates from the aecoc table set to
>NULL?
>But then, you later remove the rows anyway by adding predicates that
>exclude all rows where these fields do not have a 0 value. Wouldn't you
>get the same result by using an INNER JOIN instead of a LEFT JOIN? Or
>am I missing something?

How the data base is old, exists sadly articles that they are not codified
with the codes aecoc (they do not have description) and however have sales
reflected to the table vtdiaart (table of daily sales ) whit a code
nonexistent in the table aecoc.
The workers of the department of purchases you are updating it, but at the
moment it exists.

I believe that he was this what you asked.

Jose Antonio

Jochem

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org