--- In firebird-support@yahoogroups.com, Carsten Schäfer wrote:

> Whole query looks like:
> select count(distinct t_apos.id_apos),
> count(distinct t_Apos.f_id_auftrag),
> count(distinct f_id_kunde),
> sum(t_Apos.f_weight),
> sum(t_apos.f_quantity)
> FROM t_apos a1
> LEFT JOIN t_auftrag ON f_id_auftrag = id_auftrag
> JOIN t_lagerposzuord on id_lagerposzuord = f_id_lagerposzuord
> JOIN t_ch_apos_zuord ON id_apos = f_id_apos
> JOIN t_charge ON f_id_charge = id_charge
> WHERE (t_charge.f_id_anlage = 84 AND
> t_auftrag.f_adatum between '05.10.2004 00:00' AND
> '06.11.2004 11:23' ) AND
> id_apos > 0

Hi Carsten!

I have two problems with this query:

1) You do not say which tables the fields in the joins belong to
2) The WHERE clause contains a reference to t_auftrag

The latter is probably just me - I don't understand how something that
is on the right side of a left join - and as such should be included
whether or not there is a matching record can be combined with putting
it in the where clause which will limit the entire result set (to me
that would indicate a JOIN and not a LEFT JOIN - any limiting criteria
on the right table only I'd put in the JOIN clause). Hence, for the
part of my suggested solution I have simply ignored that part and
you'll have to figure out yourself how to include that part.

I suggest you replace sum(t_Apos.f_weight) with the following
subquery:

(select sum(a2.f_weight) from t_aPos a2
where a2.id_apos = a1.id_apos
and exists(select * from t_ch_apos_zuord az2
JOIN t_charge c2 ON a2.f_id_charge = c2.id_charge
/* maybe az2.f_id_charge */
WHERE a2.id_apos = az2.f_id_apos
and c2.f_id_anlage = 84)) as sum_f_weight

and then do the same for f_quantity. Maybe things can get optimized,
but I hope this will get you going.

Set