locked
How to select the lowest available item ID to insert in order table RRS feed

  • Question

  • Hi there

    I'm working on a library booking system......


    I have four tables and a view which i am working with 

    Product - contains all information about products with a product ID for each product

    Item - contains sequential ids for every item in the inventory, including a foreign key to on product ID to enable lookup on product details in the 'Product' tables

    Order - Contains all historical order information

    TempOrder - Contains temporary order information built up by the user before submitting the order to the 'Order' table

    vw_ItemsNotOnOrder - contains a view of all itemID's, apart from those which are on order (depicted by a NULL value for a returnDate column in 'Order'

    I am trying to access the smallest ID number from vw_ItemsNotOnOrder via a JOIN to enable me to insert this value along with other information into the 'Order' table

    My query is as follows

    INSERT INTO [ORDER] (ItemID, TakeOutDate, BringBackDate, ReturnDate, MemberID, StaffID)
    SELECT a.ItemID, GetDate(), DATEADD(Month,3,GetDate()), NULL, 1, 'JASON'
    FROM  TempOrder tmpO
    INNER JOIN
    (SELECT MIN(i.ItemID) AS ItemID, tmp.ProductID FROM TempOrder tmp 
    INNER JOIN vw_ItemsNotOnOrder i
    ON tmp.ProductID = i.ProductID
    GROUP BY tmp.ProductID) a
    on tmpO.ProductID = a.ProductID

    The smallest ID available is 4, but the following results are inputted into 'Order'

    ID  TakeOutDate BringBackDate   ReturnDate  MemberID  StaffID

    4 2012-02-23 15:56:26.867 2012-05-23 15:56:26.867 NULL 1 JASON
    4 2012-02-23 15:56:26.867 2012-05-23 15:56:26.867 NULL 1 JASON

    I want to input the ID of 4 for the first record in 'Order', but 5 for the second. I think I need a loop or something, so that the MIN(i.ItemID) is evaluated for every row. 

    Is this possible?

    Thursday, February 23, 2012 4:11 PM

Answers

  • I think I finally see what you mean.  You are after a sort of "check out" system.  In that case, try something like this:

    ;WITH CTEvw_ItemsNotOnOrder AS ( SELECT i.ItemID, i.ProductID, ROW_NUMBER() OVER (PARTITION BY i.ProductID ORDER BY i.ItemID) AS vw_ItemsNotOnOrderItemSeq FROM vw_ItemsNotOnOrder i ) INSERT INTO [ORDER] (ItemID, TakeOutDate, BringBackDate, ReturnDate, MemberID, StaffID) SELECT a.ItemID, GetDate(), DATEADD(Month,3,GetDate()), NULL, 1, 'JASON' FROM CTEvw_ItemsNotOnOrder a INNER JOIN ( SELECT tmp.ProductID, ROW_NUMBER() OVER (PARTITION BY tmp.ProductID ORDER BY (SELECT 0)) AS TempOrderItemSeq FROM TempOrder tmp ) tmpO on tmpO.ProductID = a.ProductID AND tmpO.TempOrderItemSeq = a.vw_ItemsNotOnOrderItemSeq
    ORDER BY tmpO.ProductID, tmpO.TempOrderItemSeq

    So the CTE expression enumerates all the ItemIDs in vw_ItemsNotOnOrder for each ProductID.

    The sub-query enumerates the rows in TempOrder for each ProductID.  Note that since I don't know what other columns exists in TempOrder, I used (SELECT 0) as the ORDER BY clause in the ROW_NUMBER() function, to indicate I don't care.  If you actually have columns you would like to use in that table to order your rows for each ProductID, you can use them instead.

    Putting it all together, we match by ProductID each row in TempOrder with a row in vw_ItemsNotOnOrder so that we get a 1:1 match between the number of each ProductID ordered and the number of ItemIDs available, starting with the minimum one.

    i.e. TempOrder Row1 will have the first vw_ItemsNotOnOrder.ItemID that corresponds to the ProductID.  TempOrder Row2 will have the next vw_ItemsNotOnOrder.ItemID that corresponds to the same ProductID. And so on...

    Hope that helps.  Of course you may want to change the INNER JOIN to a LEFT JOIN and find any TempOrders without corresponding  itemID's, in case you want to validate when there aren't anymore itemID's left. 


    Diane

    • Marked as answer by KJian_ Thursday, March 1, 2012 7:20 AM
    Friday, February 24, 2012 11:16 PM

All replies

  • How about CROSS APPLY with UDF?

    CROSS APPLY article:

    http://www.sqlusa.com/articles2005/crossapply/


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

    Thursday, February 23, 2012 4:59 PM
  • Can you please be a little more specific how the two tables Product and Item are related?

    What is the purpose of the sequential ID? How is it generated?

    The questions are asked to better understand the concept. Based on my current interpretation I'd question the concept: how would you deal with concurrent access, e.g. two separate processes will try to insert data for the same product (item??) in parallel? What value of MIN(i.ItemID) would you expect for each process?

    Thursday, February 23, 2012 5:25 PM
  • Before I make my suggestions, can I ask why you would be inserting into Order without first inserting into Item?  Is ItemID an IDENTITY column, a Primary Key on Item, and a Foreign Key constraint on Order?  Are multiple users able to insert at the same time?  If these conditions are all true, you can't guarantee that the next ItemID will in fact be sequential.

    Are you using SQL 2005 or higher?  If so, take advantage of the OUTPUT clause for INSERT statements to get your list of itemIDs to use in updating your Order table.

    For example,

    -- Table of Item record changes.
    DECLARE @ItemChanges AS table (
    	DDLAction varchar(6),
    	<other_columns>,
    	ProductID int,
    	ItemID int
    );
    
    -- Item Insert.
    INSERT INTO [Item] (
    	<other_columns>,
    	ProductID
    	)
    OUTPUT 
    	'INSERT' AS DDLAction,
    	INSERTED.<other_columns>,
    	INSERTED.ProductID,
    	INSERTED.record_id AS ItemID
    INTO @ItemChanges (
    	DDLAction,
    	<other_columns>,
    	ProductID,
    	ItemID
    )
    SELECT
    	<other_columns>,
    	ProductID
    <optional_from_clause>
    
    -- Order Insert.
    INSERT INTO [ORDER] (
    	ItemID, 
    	TakeOutDate, 
    	BringBackDate, 
    	ReturnDate, 
    	MemberID, 
    	StaffID
    )
    SELECT a.ItemID, 
    	GETDATE(), 
    	DATEADD(MONTH,3,GETDATE()), 
    	NULL, 
    	1, 
    	'JASON'
    FROM  TempOrder tmpO
    INNER JOIN @ItemChanges a
    ON tmpO.ProductID = a.ProductID

    Note that the DDLAction column is optional, along with any other columns you might want to reference.  I'm just including it here to demonstrate that the OUTPUT clause can be used to insert/track other things.

    Anyways, if you do not have a IDENTITY column on Item.itemID, and you have to generate these sequential IDs yourself, you can still use the above code, but also include the Item.ID in the INSERT and SELECT clauses of the Item Insert statement.  You can also use the ROW_NUMBER() clause in SQL 2005 and higher to sequence the rows in Item, and then add these values back to the current maximum.

    If you are using SQL 2000 or lower, and you want to accomplish it the way you have defined in your example, then you can lock the Item table (via  setting Transaction Isolation Level to SERIALIZABLE, starting an explicit transaction and then doing a SELECT on it), then you can either use a cursor or WHILE statement to insert your records one at a time, checking each time the max value of itemIDs.

    If you are using SQL 2005 or higher, and you want to accomplish it the way you have defined in your example, then you can lock the Item table (as specified above), read the current MAX value of ItemID, and then in your INSERT on Order, use MAX(itemID) + ROW_NUMBER() - 1 to get sequential ItemIDs.

    Hope it helps!


    Diane



    • Edited by Diane Sithoo Thursday, February 23, 2012 5:38 PM
    • Proposed as answer by Naomi N Thursday, February 23, 2012 6:05 PM
    Thursday, February 23, 2012 5:36 PM
  • LMU - Product and Item are as follows

    Product contains Product IDs and product details such as 'product name', product description'. e.g. There is a product with product id 12 which is a  blue book

    Item contains an Item ID and a product Id. There are 6 blue books, so I have Item ID 2, 3, 4, 5, 6, 7 which have the product ID 12.

    If 2 blue books are booked out (ItemID 2 & 3), and I have an order being processed which requires two blue books, then I want to get the two lowest available value ItemID's from Item (4 & 5), to place into Order, so they are booked out.

    The sequential nature doesn't really matter, but I need the value to be different.

    If you see the original post, it shows how the current method I have would return the minimum ID (4) twice. This is because the query is evaluated which looks at the view show the list of IDs which are not on order and returns the minimum ID from this list. I need to find the first two minimum IDs

    I think the process I need is 

    Evaluate the minimum ID....use this value to place in order

    Evaluate the minimum ID again........use this value to place in order.


    • Edited by Swan_E Friday, February 24, 2012 3:27 PM
    Friday, February 24, 2012 3:18 PM
  • Thanks - but i dont want to insert into Item. Item is a table of all items in a library. I want to find the lowest ItemID, but not the lowest ID for both inserts into Order.
    Friday, February 24, 2012 3:20 PM
  • You could use ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY PickAColumThatAllowsToControlTheOrderRows) as RowPos together with your temp table.

    In your final SELECT use

    SELECT a.ItemID + RowPos - 1

    Friday, February 24, 2012 4:31 PM
  • I think I finally see what you mean.  You are after a sort of "check out" system.  In that case, try something like this:

    ;WITH CTEvw_ItemsNotOnOrder AS ( SELECT i.ItemID, i.ProductID, ROW_NUMBER() OVER (PARTITION BY i.ProductID ORDER BY i.ItemID) AS vw_ItemsNotOnOrderItemSeq FROM vw_ItemsNotOnOrder i ) INSERT INTO [ORDER] (ItemID, TakeOutDate, BringBackDate, ReturnDate, MemberID, StaffID) SELECT a.ItemID, GetDate(), DATEADD(Month,3,GetDate()), NULL, 1, 'JASON' FROM CTEvw_ItemsNotOnOrder a INNER JOIN ( SELECT tmp.ProductID, ROW_NUMBER() OVER (PARTITION BY tmp.ProductID ORDER BY (SELECT 0)) AS TempOrderItemSeq FROM TempOrder tmp ) tmpO on tmpO.ProductID = a.ProductID AND tmpO.TempOrderItemSeq = a.vw_ItemsNotOnOrderItemSeq
    ORDER BY tmpO.ProductID, tmpO.TempOrderItemSeq

    So the CTE expression enumerates all the ItemIDs in vw_ItemsNotOnOrder for each ProductID.

    The sub-query enumerates the rows in TempOrder for each ProductID.  Note that since I don't know what other columns exists in TempOrder, I used (SELECT 0) as the ORDER BY clause in the ROW_NUMBER() function, to indicate I don't care.  If you actually have columns you would like to use in that table to order your rows for each ProductID, you can use them instead.

    Putting it all together, we match by ProductID each row in TempOrder with a row in vw_ItemsNotOnOrder so that we get a 1:1 match between the number of each ProductID ordered and the number of ItemIDs available, starting with the minimum one.

    i.e. TempOrder Row1 will have the first vw_ItemsNotOnOrder.ItemID that corresponds to the ProductID.  TempOrder Row2 will have the next vw_ItemsNotOnOrder.ItemID that corresponds to the same ProductID. And so on...

    Hope that helps.  Of course you may want to change the INNER JOIN to a LEFT JOIN and find any TempOrders without corresponding  itemID's, in case you want to validate when there aren't anymore itemID's left. 


    Diane

    • Marked as answer by KJian_ Thursday, March 1, 2012 7:20 AM
    Friday, February 24, 2012 11:16 PM