Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2011
    Posts
    11

    Unanswered: Lost on how to write this query

    I have three tables. One of them contains sales order info. It looks something like this:

    OrderNo (PK) ----- OrderDetail (PK) ----- ItemID (FK)
    ---1 ----------------- 1 --------------------2
    ---1 ------------------2 --------------------3
    ---2 ------------------1 --------------------3
    ---3 ------------------1 --------------------1
    ---3 ------------------2 --------------------5

    Another table contains Item information:

    ItemID (PK) ----- Description ----------- ItemType
    ---1 ------------------PC----------------Equipment
    ---2 ---------------Printer ---------------Equipment
    ---3 ------------Black Ink Cartridge-------Accessory
    ---4 ---------------Memory--------------Accessory
    ---5 -------------Graphics Card ----------Accessory
    ---6 -------------Mac Computer----------Equipment

    And a third table indicates which items are related to which other items. The RelatedItem field is an ItemID:

    ItemID(PK) ----- RelatedItem(FK)
    ---1 ----------------- 1
    ---1 ----------------- 2
    ---1 ----------------- 4
    ---1 ----------------- 5
    ---2 ----------------- 1
    ---2 ------------------2
    ---2 ----------------- 5
    ---3 ------------------2
    ---4 ------------------1
    ---5 ------------------1
    ---5 ------------------6
    ---6 ------------------6
    ---6 ------------------5

    It basically indicates which equipment an accessory is associated with. If the item is a piece of equipment, then it is associated with itself, but it can also be associated with another piece of equipment.

    I'm trying to create a query that will join these three tables, but I only want the RelatedItem to show if the RelatedItem is part of my sales order. Here is what I would want my query result set to look like for the third sales order:

    OrderNo ---- OrderDetail ---- ItemID ---- RelatedItem
    --3 ------------1 -------------1------------1
    --3 ------------2 -------------5------------1

    As opposed to this:

    OrderNo ---- OrderDetail ---- ItemID ---- RelatedItem
    --3 ------------1 -------------1------------1
    --3 ------------1 -------------1------------2
    --3 ------------1 -------------1------------4
    --3 ------------1 -------------1------------5
    --3 ------------2 -------------5------------1
    --3 ------------2 -------------5------------6

    Basically, the equipment item needs to have the RelatedItem point back to its own itemID, while each accessory item points back to the ItemID of the equipment it's associated with. I don't want to display the other records.

    Obviously, I can join the order table and the item table based on the ItemID. But how can I join to the RelatedItems table while only bringing in the RelatedItems that I need for that sales order?

    I would greatly appreciate any ideas on how to go about this.

    Thank you,

    Hammerklavier

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    SELECT O.OrderNo, O.OrderDetail, O.ItemID, R.RelatedItem
    FROM SalesOrder as O
    	INNER JOIN RelatedItems AS R ON
    		O.ItemID = R.ItemID
    	INNER JOIN SalesOrder AS SO ON
    		O.OrderNo = SO.OrderNo AND
    		R.RelatedItem = SO.ItemId
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2011
    Posts
    11
    Wim,

    Thank you for responding.

    Part of my problem is that I need the result set to have the same number of records as my orders table. Items that don't have a relateditem on the sales order should be part of the result set with a blank relateditem field rather than being left off of it.

    Using left joins, on the other hand, seems to give me too many records.

  4. #4
    Join Date
    Nov 2011
    Posts
    11
    An update that might help to explain what's going wrong.

    In the RelatedItems table, the ItemID is always an Equipment and the RelatedItem is always a Accessory. Equipments never are related to themselves.

    Unfortunately, I'm as confused as ever on how to approach this.

  5. #5
    Join Date
    Nov 2011
    Posts
    11
    Allow me to clarify...

    Sample data:

    OrderDetails
    SOID---DetailID---ItemID
    1--------1--------254
    1--------2--------260
    1--------3--------216
    1--------4--------285

    Items
    ItemID---ItemType
    254------Equipment
    257------Equipment
    260------Equipment
    216------Accessory
    285------Accessory
    287------Accessory

    RelatedItems
    ItemID--RelatedItemID
    254--------216
    254--------285
    254--------287
    257--------216
    260--------287

    What I want to see in my result set
    SOID---DetailID---ItemID--ItemType---RelatedItem
    1--------1--------254-----Equipment---254
    1--------2--------260-----Equipment---260
    1--------3--------216-----Accessory---254
    1--------4--------285-----Accessory---254

    So basically, if the ItemType is "Equipment", I want the RelatedItem to be the ItemID for that Item. If the ItemType is "Accessory", I want the RelatedItem to be the Equipment Item it relates to on that sales order.

    Here is the SQL query I'm trying to use:


    select orderdetails.SOID, orderdetails.detailid, items.itemid, Items.ItemType,
    Equipment =
    CASE Items.ItemType
    when 'Equipment' THEN Items.ItemID
    else RelatedItems.ItemID
    END
    from Orders left join OrderDetails
    on orders.SOID = orderdetails.soid
    left join Items
    on orderdetails.ItemID = items.itemid
    left join RelatedItems
    on orderdetails.ItemID = RelatedItems.RelatedItemID
    inner join OrderDetails so
    on RelatedItems.ItemID = so.ItemID and Orders.SOID = so.SOID


    With an INNER JOIN on OrderDetails so, I lose all of the Equipment items (and, I believe some Accessories that don't have a RelatedItem assigned to them). If I make it a LEFT JOIN, I bring in the Equipment Items, but end up with every RelatedItemID for every accessory, regardless of whether or not the RelatedItemID matches the ItemID for an Equipment item on that sales order (SOID).

    I greatly appreciate any time spent helping me with this.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Give this a try:
    Code:
    select OD.SOID, 
    	OD.DetailId, 
    	OD.ItemId, 
    	#Items.ItemType,
    	CASE #Items.ItemType
    		when 'Equipment' 
    			THEN OD.ItemID
    			else
    			(SELECT RI.ItemID
    			FROM #RelatedItems as RI
    				INNER JOIN #OrderDetails ON
    					RI.ItemID = #OrderDetails.ItemID AND
    					OD.SOID = #OrderDetails.SOID
    			WHERE OD.ItemID = RI.RelatedItemID
    			)
    	END	AS RelatedItem
    from #OrderDetails as OD
    	INNER JOIN #Items ON
    		OD.ItemID = #Items.ItemID
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Nov 2011
    Posts
    11
    Wim,

    When I try this, I receive this error message:

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I have tested it on the sample data you provided in your post from 14:54 and it works on that dataset.

    It's next to impossible for me to debug the script without access to your data. But I am convinced that this script may give you another insight on how to tackle this problem. In SQL-land many roads lead to Rome, so your final solution may be quite different from this script. Most likely the script is still buggy, but you might also be stumbling upon faulty data in your database.

    Maybe you won't get this error message any more by simply adding DISTINCT between "(SELECT" and "RI.ItemID". But having to add DISTINCT is al too often a good indicator for a flawed script. Use wisely and with caution.
    Last edited by Wim; 12-08-11 at 13:30. Reason: Added the sentenses about DISTINCT
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Oct 2009
    Posts
    27
    HI, This query may help you

    CREATE TABLE #OrderDetails (SOID INT, DetailID INT, ItemID INT)
    CREATE TABLE #IREMS (ItemID INT, ItemType VARCHAR(20))
    CREATE TABLE #RelatedItems (ItemID INT, RelatedItemID INT)

    INSERT INTO #OrderDetails (SOID , DetailID , ItemID )
    SELECT 1, 1, 254 UNION ALL
    SELECT 1, 2, 260 UNION ALL
    SELECT 1, 3, 216 UNION ALL
    SELECT 1, 4, 285


    INSERT INTO #IREMS (ItemID , ItemType )
    SELECT 254, 'Equipment' UNION ALL
    SELECT 257, 'Equipment' UNION ALL
    SELECT 260, 'Equipment' UNION ALL
    SELECT 216, 'Accessory' UNION ALL
    SELECT 285, 'Accessory' UNION ALL
    SELECT 287, 'Accessory'


    INSERT INTO #RelatedItems (ItemID , RelatedItemID )
    SELECT 254, 216 UNION ALL
    SELECT 254, 285 UNION ALL
    SELECT 254, 287 UNION ALL
    SELECT 257, 216 UNION ALL
    SELECT 260, 287

    SELECT DISTINCT O.SOID , O.DetailID , O.ItemID , I.ItemType,
    (CASE
    WHEN I.ItemID = O.ItemID AND I.ItemID = RI.ItemID THEN O.ItemID
    WHEN I.ItemID = O.ItemID AND I.ItemID = RI.RelatedItemID THEN RI.ItemID
    ELSE NULL END) AS RelatedItemID
    FROM #OrderDetails O
    LEFT JOIN #IREMS I ON I.ItemID = O.ItemID
    LEFT JOIN #RelatedItems RI ON (RI.ItemID = I.ItemID OR RI.RelatedItemID = I.ItemID)


    DROP TABLE #OrderDetails
    DROP TABLE #IREMS
    DROP TABLE #RelatedItems

  10. #10
    Join Date
    Nov 2011
    Posts
    11
    sql-programmers,

    Thank you for the response. I've tried your query, and it gives me too many values for the RelatedItemID field. This has been part of what makes this query very challenging for me.

    The RelatedItemID needs to be:
    1) Equal to the ItemID if the item is an Equipment item
    2) Equal to the ItemID of the Equipment item in that SOID if its RelatedItems.ItemID is equal to the Items.ItemID of the equipment item
    3) NULL (but still presented in the result set) if the Accessory item does not appear in the RelatedItems table.
    4) NULL, or anything else (but still present in the result set), in the event that the Accessory item in the SOID does not have a RelatedItems.ItemID that matches an Equipment item in the SOID. Should only be presented once in the result set.

    Any help on this would be greatly appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •