December 9, 2011 at 12:04 pm
I guess the best way to describe my problem is by giving sample data.
Orders table
SOID----DetailID--ItemID---ItemType
463-----1---------500-----Equipment
463-----2---------550-----Equipment
463-----3---------732-----Accessory
463-----4---------744-----Accessory
463-----5---------785-----Accessory
463-----6---------788-----Accessory
RelatedItems table
ItemID---RelatedItem
500------732
500------834
500------853
525------744
550------785
592------744
500------785
The ItemID in the RelatedItems table is always an equipment item. The RelatedItem is always an accessory.
I want a result set with a column that will contain a single value for each order detail.
1. If the item in the order detail is an Equipment item, then the EquipmentGroup is equal to the ItemID of that item.
2. If the item in the order detail is an Accessory item, then...
A. If the accessory item is related to an equipment item in the sales order, I want the EquipmentGroup value to be the ItemID of that equipment item.
B. If the accessory item is not related to any equipment items in the sales order, the EquipmentGroup value should be "No Group" or something along those lines.
C. If the accessory item is not in the RelatedItems table, the EquipmentGroup value should be "No Group" or "Null" or something along those lines.
But every accessory in the sales order needs to be presented in the recordset just once. That's where I'm running into difficulties.
This is the result set I would like to see for this sales order:
SOID----DetailID--ItemID---ItemType---EquipmentGroup
463-----1---------500-----Equipment--500
463-----2---------550-----Equipment--550
463-----3---------732-----Accessory--500
463-----4---------744-----Accessory--No Group
463-----5---------785-----Accessory--550
463-----5---------785-----Accessory--500
463-----6---------788-----Accessory--No Group/NULL
I would greatly appreciate any help on this.
December 9, 2011 at 12:52 pm
try something like this:
select SOID, DetailID, O.ItemID, ItemType, isnull(R.ItemID,O.ItemID) as equipGroup
from Orders O
LEFT JOIN RelatedItems R on O.ItemID = R.RelatedItem
The probability of survival is inversely proportional to the angle of arrival.
December 9, 2011 at 12:55 pm
tarr94 (12/9/2011)
I guess the best way to describe my problem is by giving sample data.
Yep, but you'll want to make it consumable so we can provide you back tested code. Check out the first link in my signature, it'll walk you through what we prefer to see to assist with code.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 9, 2011 at 2:26 pm
Having read the "Forum Etiquette" page, here are the test tables:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #orders
--===== Create the test table with
CREATE TABLE #orders
(
IDINT IDENTITY (1,1) PRIMARY KEY,
SOIDINT,
DetailIDINT,
ItemIDINT,
ItemTypeVARCHAR(15)
)
SET IDENTITY_INSERT #orders ON
INSERT INTO #orders
(ID, SOID, DetailID, ItemID, ItemType)
SELECT '1', '463', '1', '500', 'Equipment' UNION ALL
SELECT '2', '463', '2', '550', 'Equipment' UNION ALL
SELECT '3', '463', '3', '732', 'Accessory' UNION ALL
SELECT '4', '463', '4', '744', 'Accessory' UNION ALL
SELECT '5', '463', '5', '785', 'Accessory' UNION ALL
SELECT '6', '463', '6', '788', 'Accessory'
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #relatedItems
--===== Create the test table with
CREATE TABLE #relatedItems
(
IDINT IDENTITY (1,1) PRIMARY KEY,
ItemIDINT,
RelatedItemINT
)
SET IDENTITY_INSERT #relatedItems ON
INSERT INTO #relatedItems
(ID, ItemID, RelatedItem)
SELECT '1', '500', '732' UNION ALL
SELECT '2', '500', '834' UNION ALL
SELECT '3', '500', '853' UNION ALL
SELECT '4', '525', '744' UNION ALL
SELECT '5', '550', '785' UNION ALL
SELECT '6', '592', '744' UNION ALL
SELECT '7', '500', '785'
SET IDENTITY_INSERT #relatedItems OFF
select * from #orders
select * from #relatedItems
I added a couple of records to the RelatedItems table to further clarify my problem (I've updated my initial post to reflect this, as well as updating the result set that I'm hoping to see.
Thank you!
December 9, 2011 at 2:32 pm
sturner,
Thanks for the response. The problem with this result set is that it brings in both values for DetailID #4. There should be one value "No Group". Also, Detail #6 also should display "No Group" or "NULL". Only Equipment items can have an EquipmentGroup field equal to the ItemID of its own record.
December 9, 2011 at 2:53 pm
tarr94 (12/9/2011)
Having read the "Forum Etiquette" page, here are the test tables:I added a couple of records to the RelatedItems table to further clarify my problem (I've updated my initial post to reflect this, as well as updating the result set that I'm hoping to see.
Thank you!
Anyone who's going to use this, it's untested, be aware. Adjust the OBJeCT_ID tests to use the correct table and you'll need a SET IDENTITY_INSERT #orders OFF in the script as well.
Tarr, get back to you in a few on this, this code is just for anyone else who might followup in the meanwhile:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#orders','U') IS NOT NULL
DROP TABLE #orders
--===== Create the test table with
CREATE TABLE #orders
(
ID INT IDENTITY (1,1) PRIMARY KEY,
SOID INT,
DetailID INT,
ItemID INT,
ItemType VARCHAR(15)
)
SET IDENTITY_INSERT #orders ON
INSERT INTO #orders
(ID, SOID, DetailID, ItemID, ItemType)
SELECT '1', '463', '1', '500', 'Equipment' UNION ALL
SELECT '2', '463', '2', '550', 'Equipment' UNION ALL
SELECT '3', '463', '3', '732', 'Accessory' UNION ALL
SELECT '4', '463', '4', '744', 'Accessory' UNION ALL
SELECT '5', '463', '5', '785', 'Accessory' UNION ALL
SELECT '6', '463', '6', '788', 'Accessory'
SET IDENTITY_INSERT #orders OFF
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#relatedItems','U') IS NOT NULL
DROP TABLE #relatedItems
--===== Create the test table with
CREATE TABLE #relatedItems
(
ID INT IDENTITY (1,1) PRIMARY KEY,
ItemID INT,
RelatedItem INT
)
SET IDENTITY_INSERT #relatedItems ON
INSERT INTO #relatedItems
(ID, ItemID, RelatedItem)
SELECT '1', '500', '732' UNION ALL
SELECT '2', '500', '834' UNION ALL
SELECT '3', '500', '853' UNION ALL
SELECT '4', '525', '744' UNION ALL
SELECT '5', '550', '785' UNION ALL
SELECT '6', '592', '744' UNION ALL
SELECT '7', '500', '785'
SET IDENTITY_INSERT #relatedItems OFF
select * from #orders
select * from #relatedItems
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 9, 2011 at 3:01 pm
A note or two as comments in the code, but let me know if anything here ends up confusing.
SELECT
o.*,
CASE WHEN o.ItemType = 'Equipment' THEN CONVERT( VARCHAR(20), o.ItemID)
ELSE ISNULL( CONVERT( VARCHAR(20), ri.ItemID), 'No Group')
END AS EquipmentGroup
FROM
-- We need a single row 'hook' to work from for the order's overall identifier
(SELECT DISTINCT SOID FROM #orders) AS dst
JOIN
#orders AS o
ONdst.SOID = o.SOID
LEFT JOIN
#relatedItems AS ri
-- This is a subjoin to the Left Join, restricting the data.
JOIN
#orders AS o2
ONri.ItemID = o2.ItemID
ONo.ItemType = 'Accessory'
AND o.ItemID = ri.RelatedItem
AND o2.SOID = dst.SOID
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 10, 2011 at 9:49 am
Craig's solution looks perfect to me, but I think there may be a logical flaw in the question.
Here is the result set I got when testing Craig's query:
Order ID-Detail ID-Item ID--Item Type---------Equipment Group
463-----1--------500------Equipment---------500
463-----2--------550------Equipment---------550
463-----3--------732------Accessory---------500
463-----4--------744------Accessory---------525
463-----4--------744------Accessory---------592
463-----5--------785------Accessory---------550
463-----5--------785------Accessory---------500
463-----6--------788------Accessory---------788
The original question specified that there be one record in the result set for each Order Detail Record and that each accessory item appear once and only once.
Two of the Order Detail records are listed twice in the above result set, #4 & #5. The reason is this: Accessory Item #744 may belong to Equipment Item 525 or 592. Accessory Item #785 may belong to equipment item 500 or 550.
The original order contains both items 500 & 550, but only one item 785. So, which equipment item does accessory 785 go with? It could go with either. How do you want this situation represented in your data?
The original order contains item 744, an accessory, but does not contain either of the equipment items to which 744 could be an accessory. So, which equipment item should 744 be listed with, 525 or 592? Perhaps 744 should be listed as No Group, since neither 525 or 592 were in the original order.
I don't say any of this as criticism. Craig's solution is excellent, and you stated the problem very well. The sample data set is also very good, since it reveals this problem. I just wanted to point out that you need to think about how you want to deal with this particular case.
December 10, 2011 at 11:17 am
David Moutray (12/10/2011)
Craig's solution looks perfect to me, but I think there may be a logical flaw in the question.Here is the result set I got when testing Craig's query:
Odd, I got a different result set, including the NULL/NoGroups as requested and single entries.
Retesting.
The original order contains both items 500 & 550, but only one item 785. So, which equipment item does accessory 785 go with? It could go with either. How do you want this situation represented in your data?
Whichever one is in this order.
I don't say any of this as criticism. Craig's solution is excellent, and you stated the problem very well. The sample data set is also very good, since it reveals this problem. I just wanted to point out that you need to think about how you want to deal with this particular case.
Pah, if I got it wrong I got it wrong. I will be retesting shortly, it looked correct yesterday when I ran and posted it, wonder if I missed something.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 10, 2011 at 11:21 am
David Moutray (12/10/2011)
Here is the result set I got when testing Craig's query:Order ID-Detail ID-Item ID--Item Type---------Equipment Group
463-----1--------500------Equipment---------500
463-----2--------550------Equipment---------550
463-----3--------732------Accessory---------500
463-----4--------744------Accessory---------525
463-----4--------744------Accessory---------592
463-----5--------785------Accessory---------550
463-----5--------785------Accessory---------500
463-----6--------788------Accessory---------788
I'm curious how. Here is my result set, with a direct copy/paste of the corrected data build and my code:
ID SOID DetailID ItemID ItemType EquipmentGroup
----------- ----------- ----------- ----------- --------------- --------------------
1 463 1 500 Equipment 500
2 463 2 550 Equipment 550
3 463 3 732 Accessory 500
4 463 4 744 Accessory No Group
5 463 5 785 Accessory 550
5 463 5 785 Accessory 500
6 463 6 788 Accessory No Group
I wonder what's different on your system. It sounds almost like it's not enforcing the inner sub-join. #5 does repeat as you mentioned (nice catch!), but I'm really curious how you end up with values for #4 and #6. Did you by chance alter the order of the joins in the from clause? In this case the order you write this in matters.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 11, 2011 at 7:16 am
Doh! :pinch: I think I should change my screen name to Homer Simpson! :sick:
Sorry, Craig! I posted the output from Sturner's query, not yours. Your query does indeed return a single row - as required - for order detail records #4 and #6.
It does return a duplicate for order detail record #5, but that is because both parent items are present in the order.
I must say, I like this problem: it has "messy" data of the sort I see every day. Rock on! 🙂
December 12, 2011 at 7:36 am
Kraig, your solution appears to work! Thank you!
It does return a duplicate for order detail record #5, but that is because both parent items are present in the order.
I think I contradicted myself in my initial post. Just to clarify, this is correct; there should be a duplicate here.
December 12, 2011 at 11:19 am
tarr94 (12/12/2011)
Kraig, your solution appears to work! Thank you!It does return a duplicate for order detail record #5, but that is because both parent items are present in the order.
I think I contradicted myself in my initial post. Just to clarify, this is correct; there should be a duplicate here.
My pleasure. Make sure you understand why it works before you head off to production with it, because you're the one who's going to have to support it. 🙂 Let me know if you need some help grokking what I did with it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply