Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

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_CODE,
           JURISDICTIONS.NAME,
         Count(JURISDICTIONS.NAME) AS 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_CODE = JURISDICTIONS.CODE
 GROUP BY VOTERS.VST_ID,
         RESIDENTIAL_ADDRESSES.JS_CODE,
           RESIDENTIAL_ADDRESSES.IS_STANDARD,
           JURISDICTIONS.NAME) NONSTANDARD
  WHERE JURISDICTIONS.CODE = NONSTANDARD.JS_CODE
  ORDER BY JURISDICTIONS.NAME

0
restockett
Asked:
restockett
  • 2
  • 2
1 Solution
 
sdstuberCommented:
I don't think you need an outer join at all
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_code = jurisdictions.code
GROUP BY jurisdictions.code, jurisdictions.name
ORDER BY jurisdictions.name
0
 
restockettAuthor Commented:
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



0
 
sdstuberCommented:
oops, sorry, I forgot to take out the filtering criteria in the where clause

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 residential_addresses.js_code = jurisdictions.code
GROUP BY jurisdictions.code, jurisdictions.name
ORDER BY jurisdictions.name
0
 
restockettAuthor Commented:
Thank you so much.  That is perfect and I learned something too!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now