Well, yes - if you do a LEFT JOIN and check for NULL I think you can do
this to get the same result:

select BLPCID,sum(BLGROSS_SALES),sum(BLRETURNS),sum(BLNETSALES),
sum(BLDELIVERY_QUANTITY),sum(BLBACKLOG) from BACKLOG
LEFT JOIN TURNOVER_PC_SUBGROUP ON TO2CSGPID3=49 and TO2PCID = BLPCID
where BLCSGPID3=49
and BLPCID is not null
and TO2PCID is null
group by BLPCID

Though test that it gets the right result, I always prefer to use NOT
EXISTS myself (just because I'm accustomed to it, I don't know which of
the two is faster or better from a design point).

Set

Guido Klapperich wrote:

> Thanks Svein,
>
> is it possible to get the same result with a join?
>
> Regards
>
> Guido
>
> Svein Erling Tysvaer wrote:
>> That's simple Guido, just move TO2PCID and BLPCID into the where clause!
>>
>> select BLPCID,sum(BLGROSS_SALES),sum(BLRETURNS),sum(BLNETSALES),
>> sum(BLDELIVERY_QUANTITY),sum(BLBACKLOG) from BACKLOG
>> where BLCSGPID3=49 and
>> not exists(select * from TURNOVER_PC_SUBGROUP where
>> TO2CSGPID3=49 and TO2PCID = BLPCID)
>> and BLPCID is not null
>> group by BLPCID
>>
>> I added the 'and BLOPCID is not null' because I think the handling of
>> NULL values may be slightly different between NOT IN and NOT EXISTS.
>>
>> Set
>>
>> Guido Klapperich wrote:
>>> I have the following query
>>>
>>> select BLPCID,sum(BLGROSS_SALES),sum(BLRETURNS),sum(BLNETSALES),
>>> sum(BLDELIVERY_QUANTITY),sum(BLBACKLOG) from BACKLOG
>>> where BLCSGPID3=49 and
>>> BLPCID not in (select TO2PCID from TURNOVER_PC_SUBGROUP where
>>> TO2CSGPID3=49)
>>> group by BLPCID
>>>
>>> I have read in the malinglist, that FB 2.0 don't use indexes with 'not
>>> in' any more. Therefore I want to use 'not exists', but I'm not able to
>>> convert the query. Can anybody help me?
>>>
>>> Regards
>>>
>>> Guido