Re: SQL query correctness/efficiency of my query

The tables in question remind me of tables in an MRP product that was once called JIT. The key of the item_master table is actually item_number and plant. If your item_master table has parts for more than one plant in it then you need to add plant to the join conditions since the plant column also exists in cost_item and item_comments.

Even if your item_master has data for only one plant if the PK is item_no and plant then you should use both columns in the join for good form and so that the query produces correct results when data for a second plant is inserted at some future point in time.

There should be indexes that inlcude item_no and plant on all three tables of your join. If there are then valid statistics on the tables should result in the CBO choosing the optimal plan.

IMHO -- Mark D Powell -- Received on Mon Sep 26 2005 - 18:32:52 CDT