[thelist] Query Design Question
Luther, Ron
Ron.Luther at hp.com
Tue Feb 3 09:33:54 CST 2009
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.
More information about the thelist
mailing list