How do you produce a Master/Detail report using SQL? (I'm an Oracle user, if that matters.) For example, I need a Developer/Project report and I have the following tables --
Employee Table: Emp_ID_number Emp_Name Emp_Title
Table: Proj_Emp_ID_Number (Foreign Key) Proj_Number Proj_Name Proj_Deadline
I need a report that lists the Emp_ID_Number, Emp_Name, Emp_title on one line then the associated projects (Proj_Number, Proj_Name, Proj_Deadline) assigned to that Developer listed under the Employee info.
Output may look as follows --
123 John Doe Application Devel Lvl 2 HRTS Time Sheet for HR 3/15/02 FIYE Accounting Year End 6/1/02
456 Joe Jones Web Developer Lvl 1 CRWP Returns Web Page 4/30/02 RPWB Web-based Reporting 12/31/02
Short answer to a long question: do this with application program logic. I'm sorry if this sounds like an apology for something that's too hard to do in SQL. It's more than that. I'm sorry too if this sounds like a rant -- it isn't meant to. You will save yourself some grief and ultimately thank me. Don't try to do this with SQL (but see note below).
SQL queries produce result sets, not reports. Result sets look exactly like tables, with rows and columns. The simplest strategy would be just to send the result set table data to the printer, but this gives the "repeated" Master values for each Detail that you are trying to avoid. It's not a big deal until you exceed the width of the report (even in landscape orientation; and think of all the wasted ink!) -- naturally, you start looking to "fold" the lines or group them somehow.
The best way to suppress the repeating "master" data is to write logic in your application program to do it. No matter which application language you have, whether COBOL, Crystal Reports, or ColdFusion, it will have some way of printing a table. Some easier than others.
Here's how you would do your report in ColdFusion --
<CFOUTPUT QUERY="getData" GROUP="Employee"> #Emp_ID_number# #Emp_Name# #Emp_Title# <CFOUTPUT> #Proj_Number# #Proj_Name# #Proj_Deadline# </CFOUTPUT> </CFOUTPUT>
There's also a technique called Data Shaping which requires Microsoft's ADO 2.0, but I don't know anything about it, except that it looks pretty complicated.
Note: Actually, there is a way to do it in SQL, but it's ugly.
select Emp_ID_number||Emp_Name||Emp_Title as printline , Emp_ID_number as sortkey from Employee union all select ' '||Proj_Emp_ID_Number||Proj_Number ||Proj_Name||Proj_Deadline as printline , Proj_Emp_ID_Number||Proj_Number as sortkey from Projects order by 2
I did warn you that it's ugly. Probably slow, too. Way harder to make changes to in the long run. And the sortkey prints (which is why I put it to the right of the printline; maybe it'll be off the printed page).
For More Information
- What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
- 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.
This was first published in March 2002