Solved

Create and outer join in sql for Oracle

Posted on 2011-03-22
4
307 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query 7 49
Trying to understand why my Index is so large 12 52
Dbms_job.change procedure 16 36
Need to Implment Subset of Oracle Schema to Read-Only version 7 37
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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.

734 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