Help for queries with different results, but I will the same

[Luigi Siciliano]

Hi,

I have two queries that return different results:

The First:

select
sum(iif(td.partite = 1, dc.Importo * 1, dc.Importo * -1)) as Totale
from
doc_testa dt,
doc_corpo dc,
Tipo_Documenti td
where
dt.id = dc.doc_testa_id
and dt.documento_id = td.id
and (extract (year from dt.data) = 2025)
and td.Partite > 0
and dc.ritenuta = 1

returns 33069.07 as Totale

The Second:

select
sum(iif(td.partite = 1, dc.Importo * 1, dc.Importo * -1)) as Compenso,
a.ss_campo
from
doc_testa dt,
doc_corpo dc,
Tipo_Documenti td,
Articoli a
where
dt.id = dc.doc_testa_id
and dt.documento_id = td.id
and (extract (year from dt.data) = 2025)
and td.Partite > 0
and dc.ritenuta = 1
and dc.Articolo_id = a.id
group by
a.ss_campo

returns 10 rows. The sum of the “Compeno” field for all 10 rows is 33295.89.

I expect the results of the two queries to be the same = 33069.07 or
33295.89!

In the Articoli table there are 3 rows with ss_campo = null.

What I'm wrong?

Thanks

--
Luigi

[Tomasz Tyrakowski]

On 2.07.2026 at 15:23, Luigi Siciliano wrote:
> Hi,
>
> I have two queries that return different results:
>

> [...]
Join conditions differ. There is an additional clause:

dc.Articolo_id = a.id

in the second query.

regards
Tomasz

[Luigi Siciliano]

hI,

Il 02/07/2026 16:00, Tomasz Tyrakowski ha scritto:
> Join conditions differ. There is an additional clause:
>
> dc.Articolo_id = a.id
>
> in the second query.
>

Okay, this is used to break down the results of the first query: to
distinguish and summarize the total from the first query.

Should I use this join in a different way?

Thanks

--
Luigi

[Tomasz Tyrakowski]

Without the knowledge about your data model, I don't know. The fact is,
your second query incorporates Articoli and if there are some rows in
doc_corpo, which don't have corresponding rows in Articoli, those won't
be taken into account in the second query, but will be processed in the
first one. So check for that - see if you've got dc_corpo without
corresponding Articoli (null Articolo_id or a value not matching any
Articoli.id).
Try to transform your first query to use Articoli like the second one
(but without grouping) - I bet the sum will be different.
If you need the grouping by Articoli and still want to sum doc_corpo
without corresponding Articoli, you'll need to use left join, something
like:

select
sum(iif(td.partite = 1, dc.Importo * 1, dc.Importo * -1)) as Compenso,
a.ss_campo
from

doc_testa dt join doc_corpo dc on (
dt.id = dc.doc_testa_id

and (extract (year from dt.data) = 2025)

and dc.ritenuta = 1
) join Tipo_Documenti td on (
dt.documento_id = td.id
and td.Partite > 0
) left join Articoli a on (
dc.Articolo_id = a.id
)
group by a.ss_campo

For doc_corpo without matching Articoli you'll have null-labeled group.

regards
Tomasz

[Luigi Siciliano]

Il 02/07/2026 22:20, Tomasz Tyrakowski ha scritto:
> On 2.07.2026 at 16:33, Luigi Siciliano wrote:
>> hI,
>>
>> Il 02/07/2026 16:00, Tomasz Tyrakowski ha scritto:
>>> Join conditions differ. There is an additional clause:
>>>
>>> dc.Articolo_id = a.id
>>>
>>> in the second query.
>>>
>> Okay, this is used to break down the results of the first query: to
>> distinguish and summarize the total from the first query.
>>
>> Should I use this join in a different way?
>>
> Without the knowledge about your data model, I don't know. The fact
> is, your second query incorporates Articoli and if there are some rows
> in doc_corpo, which don't have corresponding rows in Articoli, those
> won't be taken into account in the second query, but will be processed
> in the first one. So check for that - see if you've got dc_corpo
> without corresponding Articoli (null Articolo_id or a value not
> matching any Articoli.id).

Great suggestion: I found a row in the doc_corpo table where Articolo_id
is null :(

Thanks

--
Luigi