Hi All,
I have a question that is probably pretty easy, so bear with me here... All I want to do is take an OrderNum I am given and return all of the ProductTitle(s). Obviously all of the tables below have more fields, but I have only listed the important ones. After the table structure, I explain what I currently do.
My DB Table Structure:
Since OrderNum can appear multiple times in the table, it is NOT the primary key for OrdersProcessing, but this is how I want it, because an Order may contain more than one Item (ItemID). So since there may be more than a 1 to 1 relationship at the top, my Stored Procedure creates a TempTable for every level of the drill down to ProductTitle. For the first level:
1.) I create a table called @ItemsFound
2.) I do a select on the Orders Table to find all of the ItemIDs that match that OrderNum (that may appear more than once, possibly giving us multiple ItemIDs at this point)
3.) Insert those ItemIDs into the @ItemsFound table.
4.) Create another tempTable called @InventoryFound.
5.) WHILE loop through the @ItemsFound table stopping at each ItemID and getting the correct InventoryID and INSERTING it into @InventoryFound.
Then I repeat that whole process for the next step until I'm all the way down to ProductTitle, and then I have a list of the ProductTitles I want for the OrderNum that may have contained more than one Item...
Isn't there a way to do this more efficiently with less creating of temp tables. Maybe even a single query? I don't need the whole query written or anything, just the logic behind how to do maybe one step of the way, and then I can run with it.
I'll be happy to provide more detail if this isn't clear enough. Thanks for any help.
I have a question that is probably pretty easy, so bear with me here... All I want to do is take an OrderNum I am given and return all of the ProductTitle(s). Obviously all of the tables below have more fields, but I have only listed the important ones. After the table structure, I explain what I currently do.
My DB Table Structure:
Code:
Orders:
OrderID
OrderNum (This is the field I am bringing into the StoredProc -- It can appear multiple times in the table)
ItemID
Items:
ItemID
InventoryID
Inventory:
InventoryID
ProductID
Products:
ProductID
ProductTitleSince OrderNum can appear multiple times in the table, it is NOT the primary key for OrdersProcessing, but this is how I want it, because an Order may contain more than one Item (ItemID). So since there may be more than a 1 to 1 relationship at the top, my Stored Procedure creates a TempTable for every level of the drill down to ProductTitle. For the first level:
1.) I create a table called @ItemsFound
2.) I do a select on the Orders Table to find all of the ItemIDs that match that OrderNum (that may appear more than once, possibly giving us multiple ItemIDs at this point)
3.) Insert those ItemIDs into the @ItemsFound table.
4.) Create another tempTable called @InventoryFound.
5.) WHILE loop through the @ItemsFound table stopping at each ItemID and getting the correct InventoryID and INSERTING it into @InventoryFound.
Then I repeat that whole process for the next step until I'm all the way down to ProductTitle, and then I have a list of the ProductTitles I want for the OrderNum that may have contained more than one Item...
Isn't there a way to do this more efficiently with less creating of temp tables. Maybe even a single query? I don't need the whole query written or anything, just the logic behind how to do maybe one step of the way, and then I can run with it.
I'll be happy to provide more detail if this isn't clear enough. Thanks for any help.
