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

An OUTER JOIN using data that doesn't exist

This is probably a common situation where we want to see a group on a report that represents no records in a category...

if none exists, just to account for these. For example, we normally have five group types for records, but sometimes there are no records for a certain type, and I want to show this on my report. I know there are some outer joins one can do on tables, but I don't have a nice neat table to join to. I'm using Crystal reports.

You're right that an OUTER JOIN will do what you want. Unfortunately, you're also right that it needs a "nice neat table" in order to work. You either have to have a table, or generate it somehow.

You say sometimes the data does not contain records for all group types. This suggests that there's a history file or something similar, where there's at least one record of every group type. Run this query once:

select distinct grouptype, grouptypename
  from historytable

Save the output as your group type table, and write the LEFT JOIN as you would normally.

If that's not possible (perhaps you do not have access to the database to create a new table), then you still might be able to generate the groups.

I know that some databases will let you select rows "out of thin air," as it were. (I'm sorry, I don't know offhand which ones do, but Microsoft SQL Server is one of them.) This refers to the ability to run a query like:

select  1        as grouptype
     , 'Group 1' as grouptypename

Note there is no FROM clause! If your database lets you do this (and it sure won't take much to find out, eh), then you could solve your problem as follows:

select g.grouptypename
     , sum(d.datacol) 
  from ( select  1        as grouptype
              , 'Group 1' as grouptypename
       union all
         select  2        
              , 'Group 2'
       union all
         select  3        
              , 'Group 3'
       union all
         select  4        
              , 'Group 4'
       union all
         select  5        
              , 'Group 5' )  g
left outer
  join yourdatatable d
    on g.grouptype = d.grouptype
group
    by g.grouptypename

For More Information


This was last published in March 2003

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.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close