Mike Hughes
Registered User.
- Local time
- Today, 18:51
- Joined
- Mar 23, 2002
- Messages
- 493
I’m trying to get the number of cases with an establishment type P and a case type A for each district (DO). The problem is if any district does not have any cases with establishment type P and case type A the query just jumps over that district. It doesn’t list it the district showing it as 0 for cases.
Below is the query. Could someone tell me how to change the query so that if the district has no cases with establishment type P and case type A it returns a 0 for that district.
Thanks MIke
SELECT
OA.DO,
NOLDBA_INT_CASE_STATUS.ESTABLISH_TYPE AS P,
NOLDBA_INT_CASE_STATUS.CASE_TYPE AS A,
Count(NOLDBA_INT_CASE_STATUS.CASE_ID) AS CASES INTO PA
FROM NOLDBA_INT_CASE_STATUS RIGHT JOIN OA ON NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE = OA.DO
GROUP BY OA.DO, NOLDBA_INT_CASE_STATUS.ESTABLISH_TYPE, NOLDBA_INT_CASE_STATUS.CASE_TYPE, NOLDBA_INT_CASE_STATUS.CASE_STATUS
HAVING (((NOLDBA_INT_CASE_STATUS.ESTABLISH_TYPE)="P") AND ((NOLDBA_INT_CASE_STATUS.CASE_TYPE)="A") AND ((NOLDBA_INT_CASE_STATUS.CASE_STATUS)="O"));
Below is the query. Could someone tell me how to change the query so that if the district has no cases with establishment type P and case type A it returns a 0 for that district.
Thanks MIke
SELECT
OA.DO,
NOLDBA_INT_CASE_STATUS.ESTABLISH_TYPE AS P,
NOLDBA_INT_CASE_STATUS.CASE_TYPE AS A,
Count(NOLDBA_INT_CASE_STATUS.CASE_ID) AS CASES INTO PA
FROM NOLDBA_INT_CASE_STATUS RIGHT JOIN OA ON NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE = OA.DO
GROUP BY OA.DO, NOLDBA_INT_CASE_STATUS.ESTABLISH_TYPE, NOLDBA_INT_CASE_STATUS.CASE_TYPE, NOLDBA_INT_CASE_STATUS.CASE_STATUS
HAVING (((NOLDBA_INT_CASE_STATUS.ESTABLISH_TYPE)="P") AND ((NOLDBA_INT_CASE_STATUS.CASE_TYPE)="A") AND ((NOLDBA_INT_CASE_STATUS.CASE_STATUS)="O"));