[thelist] Query Design Question

[Luther, Ron]

Hi Folks,


Need some input here.

Lets say I have two tables, a simplified Orders table:

Ord_Nbr    Part_Nbr   Open_Qty
  111		AAA		10
  222		AAA		 5
  333		CCC		25


And a simplified Inventory table:

Part_Nbr	Inv_Qty
  AAA		   25
  BBB		   42


Now, lets say I have two clients who want to see two different reports:

Lucy wants to see all open orders, along with any inventory that might be available to fulfill those orders. Not getting fancy with promising inventory or decrementing on-hand quantities just yet, just looking for visibility on this report. E.g. 

Ord_Nbr    Part_Nbr   Open_Qty	Inv_Qty
  111		AAA		10		  25
  222		AAA		 5		  25
  333		CCC		25

Ricky, on the other hand, is an inventory manager.  He wants to see all of his inventory, along with any open orders against that stock.  (For this report the details are fine.  We'll use the sum of the order_qty in a separate dashboard report.)  E.g.

Part_Nbr	Inv_Qty	Ord_Nbr	Open_Qty
  AAA		   25		  111		   10
  AAA		   25		  222		    5
  BBB		   42


Now, I would normally do this by building two data models; one with the tables left-joined and one with the tables right-joined and run one report off each data model.


I think other prople are planning to propose an alternative design where they would build ONE data model using an outer join to connect the two tables and then construct Lucy's report by adding a 'where Open_Qty > 0' filter and construct Ricky's report by using a 'where Inv_Qty > 0' filter.

Is that really a better approach?  (That would be okay.  I don't mind being wrong.  I'm just not seeing it.)

Any theoretical performance advantages for one approach over the other?  (I would think the outer-join would put me in full table scan mode for all the reports and the left-join / right-join would be more efficient.  Or am I out in left field again?)


Thanks!
RonL.