Q
Problem solve Get help with specific problems with your technologies, process and projects.

Another FULL OUTER JOIN example

I am trying to create a query which should return four columns. I would be glad to even have just three columns, since the total can be computed in the display table. So far I have a query which returns just two rows under the same column. Am stuck here, any help is appreciated.

I am trying to create a query which should return four columns --

Facility
NoOfActiveApplicants
NoOfArchivedApplicants
TotalApplicants

I would be glad to even have just three columns, since the total can be computed in the display table (ColdFusion interface).

So far I have the following query, which returns just two rows with both NoOfActiveApplicants and NoOfArchivedApplicants under the same column.

select
  NoOfApplicants= sum(case when a.id <> 0 
                    then 1 else 0 end) , 
  Facility= case when c.facility is null 
          then c.JobDBFacilityName 
          else c.facility end  
from tblapplicant a, 
     tblJobDB b, 
     tblfacilities c 
where a.jobid = b.jobid
and b.facility = c.facilityid
group by c.facilityid, c.JobDBFacilityName,
         c.facility, b.facility
union all
select
  NoOfApplicants= sum(case when a.id <> 0 
                    then 1 else 0 end) , 
  Facility= case when c.facility is null 
          then c.JobDBFacilityName 
          else c.facility end  
from tblArchiveapplicant a, 
     tblArchiveJob b, 
     tblfacilities c 
where a.JobVacancyNumber = b.JobVacancyNumber
and b.facility = c.facilityid
group by c.facilityid, c.JobDBFacilityName, 
         c.facility, b.facility
order by facility

Am stuck here, any help is appreciated. Thanks in advance!

One way to solve this is to use additional "placeholder" column values (NULLs or zeroes, your choice) in the two halves of your UNION, so that each half produces its amount in the appropriate active or archive column, like this:

Facility Active Archive
  aaa      23      0
  aaa       0     17
  bbb      11      0
  ccc       9      0
  ccc       0     37

The zeroes that you see above are the "placeholder" amounts produced by each half of the UNION. Then do a SELECT of the UNION, with a GROUP BY on Facility, and SUM() functions on the data columns, to "collapse" the data into one row per Facility.

Or, you could try the FULL OUTER JOIN approach.

select coalesce(active.facility
              , archive.facility) 
                      as facility
     , coalesce(active.apps 
              , 0 )   as NoOfActiveApplicants            
     , coalesce(archive.apps 
              , 0 )   as NoOfArchivedApplicants   
  from (
       select sum(case when a.id <> 0 
                       then 1 else 0 end) 
                    as apps
            , coalesce(c.facility 
                     , c.JobDBFacilityName)
                    as facility   
         from tblapplicant a, 
              tblJobDB b, 
              tblfacilities c 
        where a.jobid = b.jobid
          and b.facility = c.facilityid
       group 
           by coalesce(c.facility 
                     , c.JobDBFacilityName)
       ) as active                     
full outer
  join (
       select sum(case when a.id <> 0 
                       then 1 else 0 end) 
                    as apps
            , coalesce(c.facility 
                     , c.JobDBFacilityName)
                    as facility   
         from tblArchiveapplicant a, 
              tblArchiveJob b, 
              tblfacilities c 
        where a.JobVacancyNumber = b.JobVacancyNumber
          and b.facility = c.facilityid
       group 
           by coalesce(c.facility 
                     , c.JobDBFacilityName)
       ) as archive
    on active.facility = archive.facility
order
    by 1 

The FULL OUTER JOIN is, for me anyway, the more elegant way to solve this problem.

This was last published in August 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close