Solved

Create and outer join in sql for Oracle

Posted on 2011-03-22
4
308 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

717 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