Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Create and outer join in sql for Oracle

Posted on 2011-03-22
4
Medium Priority
?
310 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 1000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

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.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

609 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