Hi Alan!

Try

SELECT DISTINCT EBL.ID, EBL.BATCHCOUNT
, COUNT(DISTINCT EI.<PrimaryKey>)
, EBL.BTYPE
FROM ERSBATCHESLOG EBL
LEFT JOIN ERSVOUCHERBATCH EVB ON EVB.FKEXPORTLOG=EBL.ID
LEFT JOIN ERSINVOICE EI ON EI.FKBATCH = EVB.ID
GROUP BY 1, 2, 4

I think there should be other ways to do this as well.

HTH,
Set

Alan McDonald wrote:

>> At 10:54 PM 29/07/2008, you wrote:
>>> This was was my old FB1.5 query:
>>> SELECT COUNT(*) FROM ERSINVOICE WHERE ERSINVOICE.FKBATCH IN
>>> (SELECT ERSVOUCHERBATCH.ID FROM ERSVOUCHERBATCH WHERE
>>> ERSVOUCHERBATCH.FKEXPORTLOG=[!!!!]ERSBATCHESLOG.ID)
>>>
>>> can someone throw this into the EXISTS syntax? I'm having a brain
>> explosion
>>> I'm tryin to get
>>> THE NUMBER OF INVOICES
>>> WHERE THE INVOICE BATCH NUMBER
>>> IS IN THE LIST OF BATCH IDS WHICH ARE IN THE BATCH LOG
>> SELECT COUNT(*) FROM ERSINVOICE inv
>> WHERE exists (
>> (SELECT 1 FROM ERSVOUCHERBATCH batch
>> WHERE batch.FKEXPORTLOG=inv.ERSBATCHESLOG.ID)
>>
>> The good news is that, by correcting the identifier syntax in your
>> original query (where [!!!!] is marked), you'll get the engine to run
>> the EXISTS() version automagically.
>>
>> ./h
>>
>
> hmm, no I should have been clearer, it's actually a sub query. the whole
> thing is:
> SELECT ID
> , BATCHCOUNT
> , (SELECT COUNT(*) FROM ERSINVOICE WHERE ERSINVOICE.FKBATCH IN
> (SELECT ERSVOUCHERBATCH.ID FROM ERSVOUCHERBATCH WHERE
> ERSVOUCHERBATCH.FKEXPORTLOG=ERSBATCHESLOG.ID))
> , BTYPE
> FROM ERSBATCHESLOG
>
> so the identifiers are correct, but it might be a double EXISTS clause.
> BTW this runs very fast in 1.5 but it's a dog in 2.1
> Alan