Presents your 
SQL SERVER E-NEWSLETTER for March 23, 2004

<------------------------------------------->
 
Applying relational multiplication to hands-on problems 
 
Relational multiplication helped me solve a problem at work recently, 
and I thought my fellow SQL Server developers might benefit from 
hearing about my quandary. But first, I'll offer a very brief 
explanation about relational multiplication. 
 
You should know that relational multiplication is most often an error. 
For example: 
 
SELECT * FROM CUSTOMERS, ORDERS 
 
There is no JOIN clause. Therefore, this query joins every row in 
Customers with every row in Orders. It's called relational 
multiplication because the row count of the result set is equal to the 
row count from table A multiplied by the row count from table B. 
Assuming that Customers contains 1,000 rows and Orders contains 5,000 
rows, this query will return 5,000,000 rows. 
 
HOW RELATIONAL MULTIPLICATION SAVED THE DAY 
 
My colleagues and I encountered a problem concerning a report that our 
application was going to produce. The report was based on a date-range 
query. Each row in the query, which corresponded to a single customer, 
generated its own "report." (The whole batch was actually a single 
report, but after each row, there was a page break, so they were easy 
to separate.) 
 
Things got interesting when we were instructed to print three copies of 
each "report," with each copy containing a different report footer: 
Customer Copy, ABC's copy (i.e., the company's copy), and Keep this 
copy for your files. 
 
My first solution was simple: Run the report three times, passing a 
parameter in, and then assigning the parameter to a text box on the 
report footer. It worked, but there was a problem. The user would then 
have to collate the three reports into groups for each customer, which 
may not be a big deal if you have 12 customers, but it's no fun with 
100-plus customers. 
 
After trying various approaches without much success, my colleague Mary 
came up with an ingenious solution. She created a simple table called 
ReportFooters and added three rows, plugging in the text listed above. 
Then she included this table in the query underlying the 
report--without a JOIN clause. We changed the text box on the report 
footer to reflect this new column, and we got three copies per customer 
that were already collated and contained one of the desired report 
footers. 
 
Arthur Fuller has been developing database applications for more than 
20 years. He frequently works with Access ADPs, Microsoft SQL 2000, 
MySQL, and .NET. 

----------------------------------------