restockett
asked on
Create and outer join in sql for Oracle
I have written the following query to count the number of non standard addresses by county. However it is only listing the counties that have some non standard addresses. I also want it to include counties that have 0 non standard address. What do I need to add to the following query to have it list all 82 counties and put a 0 in the record for the county with 0 non standard addresses and the number in the county record for those with greater than 0.
This is the a sample of the result of the query now:
CODE NAME COUNTOFNS
2 Alcorn 80
4 Attala 409
But it should read like
CODE NAME COUNTOFNS
1 Adams 0
2 Alcorn 80
3 Amite 0
4 Attala 409
This is what I have written so far:
SELECT JURISDICTIONS.CODE, JURISDICTIONS.NAME, NONSTANDARD.COUNTOFNS
FROM JURISDICTIONS,
(SELECT RESIDENTIAL_ADDRESSES.JS_C
JURISDICTIONS.NAME,
Count(JURISDICTIONS.NAME) AS COUNTOFNS
FROM VOTERS, RESIDENTIAL_ADDRESSES,JURI
WHERE VOTERS.RESADR_ID = RESIDENTIAL_ADDRESSES.ID
AND IS_STANDARD = 0
AND VOTERS.VST_ID = 1
AND RESIDENTIAL_ADDRESSES.JS_C
GROUP BY VOTERS.VST_ID,
RESIDENTIAL_ADDRESSES.JS_C
RESIDENTIAL_ADDRESSES.IS_S
JURISDICTIONS.NAME) NONSTANDARD
WHERE JURISDICTIONS.CODE = NONSTANDARD.JS_CODE
ORDER BY JURISDICTIONS.NAME
This is the a sample of the result of the query now:
CODE NAME COUNTOFNS
2 Alcorn 80
4 Attala 409
But it should read like
CODE NAME COUNTOFNS
1 Adams 0
2 Alcorn 80
3 Amite 0
4 Attala 409
This is what I have written so far:
SELECT JURISDICTIONS.CODE, JURISDICTIONS.NAME, NONSTANDARD.COUNTOFNS
FROM JURISDICTIONS,
(SELECT RESIDENTIAL_ADDRESSES.JS_C
JURISDICTIONS.NAME,
Count(JURISDICTIONS.NAME) AS COUNTOFNS
FROM VOTERS, RESIDENTIAL_ADDRESSES,JURI
WHERE VOTERS.RESADR_ID = RESIDENTIAL_ADDRESSES.ID
AND IS_STANDARD = 0
AND VOTERS.VST_ID = 1
AND RESIDENTIAL_ADDRESSES.JS_C
GROUP BY VOTERS.VST_ID,
RESIDENTIAL_ADDRESSES.JS_C
RESIDENTIAL_ADDRESSES.IS_S
JURISDICTIONS.NAME) NONSTANDARD
WHERE JURISDICTIONS.CODE = NONSTANDARD.JS_CODE
ORDER BY JURISDICTIONS.NAME
ASKER
it is still just giving me the counties with non standard.
this:
CODE NAME COUNTOFNS
2 Alcorn 80
4 Attala 409
instead of this:
CODE NAME COUNTOFNS
1 Adams 0
2 Alcorn 80
3 Amite 0
4 Attala 409
this:
CODE NAME COUNTOFNS
2 Alcorn 80
4 Attala 409
instead of this:
CODE NAME COUNTOFNS
1 Adams 0
2 Alcorn 80
3 Amite 0
4 Attala 409
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much. That is perfect and I learned something too!
try this...
SELECT jurisdictions.code,
jurisdictions.name,
COUNT(CASE WHEN is_standard = 0 AND voters_vst_id = 1 THEN jurisdictions.name ELSE NULL END)
countofns
FROM voters, residential_addresses, jurisdictions
WHERE voters.resadr_id = residential_addresses.id
AND is_standard = 0
AND voters.vst_id = 1
AND residential_addresses.js_c
GROUP BY jurisdictions.code, jurisdictions.name
ORDER BY jurisdictions.name