[firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?

['Softtech Support' stwiz...@att.net [firebird-support]]

Greetings All,

Firebird 1.5.3 (Yes I know it is old)

Using the following syntax with 09/01/14 and 09/04/14 for the parameters 
fetches 20 distinct records

      SELECT DISTINCT DCD.ACCT_ID, AC.CLT_ID
                 FROM DEBTOR_CASE_DEBT DCD
                 JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
                  AND AC.CASE_ID = DCD.CASE_ID
                WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
                  AND DCD.STATUS_CODE = 'B'

Using this syntax with 09/01/14 and 09/04/14 for the parameters fetches a count 
of 32

      SELECT DISTINCT COUNT(DCD.PERSON_ID)
                 FROM DEBTOR_CASE_DEBT DCD
                 JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
                  AND AC.CASE_ID = DCD.CASE_ID
                WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
                  AND DCD.STATUS_CODE = 'B'

How do I accurately return the correct count using the COUNT() function?  In 
this case it should beturn 20 not 32

Any ideas appreciated.

Thanks,
Mike

---
This email is free from viruses and malware because avast! Antivirus protection 
is active.
http://www.avast.com