Solved

Create and outer join in sql for Oracle

Posted on 2011-03-22
4
306 Views
Last Modified: 2012-05-11
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
Comment
Question by:restockett
  • 2
  • 2
4 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 35191343
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
 

Author Comment

by:restockett
ID: 35191399
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 total points
ID: 35191454
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
 

Author Closing Comment

by:restockett
ID: 35191479
Thank you so much.  That is perfect and I learned something too!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
make null the repeated levels 2 31
Syntax for query to update table 2 30
Select values in a row based on values in another row in sql 4 26
SQL Syntax 24 46
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question