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

Subject	How do I return an accurate COUNT(*) when a JOIN is involved?
Author	Softtech Support
Post date	2014-10-13T18:50:38Z

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.