Hi Carsten,

> > You're joining more tables then the two Customers and Orders tables?
> > Looking at you're query i understand you need only those two for these
> > results. Could you post the whole query?
> >
> 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
>
> This is dynamcily created, so the join and the where clause can look
> different for each query.
> Problem is the n:n relationship between t_apos,t_ch_apos and t_charge,
> because of this i got duplicate lines. (Only difference is a field
> f_id_charge that is not relevant for the result)

First build base query with your needed tables for the resulting data and
then use the other tables in the EXISTS() predicate:

Something like this :

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)
WHERE
EXISTS(SELECT 1
FROM
t_lagerposzuord
JOIN t_ch_apos_zuord ON (id_apos = f_id_apos)
JOIN t_charge ON (f_id_charge = id_charge)
WHERE
id_lagerposzuord = f_id_lagerposzuord) and
(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

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81