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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.