Arno Brinkman wrote:

> 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
>

This works.
Thank you.

mfg
Carsten