Don't Let Users Wreck Their Joins

Published: January 26, 2011

by Ted Holt

You're swamped with work, and here comes Harold in Accounts Receivable yet again for help with some query he threw together that doesn't work correctly. Harold's not a bad guy--his wife, his kids, and his dog adore him. He just doesn't understand computer stuff. Here's a way you can help him help you.

Harold's problem is that he doesn't understand how to join files properly. What you need is a way to store join field information on the system. That is, you need a way to store the fact that file A and file B join over field C and field D so Harold doesn't have to know that information. And here is that way.

Let's say you have four physical files (or tables, in database jargon) with related data.

• Sales order headers
• Sales order details
• The customers to whom you sell
• The items you sell

Furthermore:

• One sales order header relates to one or more sales order details by a common sales order number
• One sales order header relates to one customer by a common company number and customer number
• One sales order detail relates to one item by a common item number

This makes perfect sense to you, but not to Harold. To help him, define the joins for him, like this:

create view slsordv1 as
(select 
   oh.ORDERNO,
   oh.CUSTPO, 
   oh.STATUS as OrderStat, 
   oh.ORDERDATE,
   oh.SHIPDATE,
   od.LINENO,
   od.ITEMNO, 
   it.DESCRIP as ItemDesc,
   it.CLASS as ItemClass,
   it.COST as BaseCost,
   it.PRICE as BasePrice,
   it.MINORDQTY, 
   it.MAKEBUY, 
   it.WEIGHT, 
   it.DISCOUNT,
   it.VMI, 
   it.ACTIVE, 
   od.QTYORDERED, 
   od.QTYSHIPPED, 
   od.PRICE,
   oh.COMPANYNO, 
   oh.CUSTOMERNO, 
   cus.CUSNAM,
   cus.BILLSTREET, 
   cus.BILLCITY, 
   cus.BILLSTATE, 
   cus.BILLZIP, 
   cus.SHIPSTREET, 
   cus.SHIPCITY, 
   cus.SHIPSTATE, 
   cus.SHIPZIP, 
   cus.TYPE as CusType
from salesordh as oh
left outer join salesordd as od
using (orderno)
left outer join customers as cus
using (companyno, customerno)
left outer join items as it
using (itemno)
)

Create view builds an unkeyed logical file that Harold can query as he would a physical file. Harold operates under the illusion that all of that data is stored in one big database file, and that means no joins. Harold can select fields (columns), select records (rows), sort, etc., and you can work on something more challenging.

Have some compassion. Don't make Harold join physical files.

RELATED STORIES

Subqueries vs. Joins

Redundant Join Criteria: Good or Bad Idea?

A Database Union is Not a Join

Missing In Action: The Full Outer Join

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot