June 30, 2010 at 11:01 am
Hello,
I am relatively new to writing sql queries and this particular one has me stumped. Any help is greatly appreciated.
I have orders come in and it creates a record in the top table. (see attached diagram)
The order has a list of items which are listed in the Items table with ItemID as the pk and OrderID as a fk.
The ItemID is a fk (and also unique) to multiple other tables based on the Item type where details for that item are stored. I need a way to pull a value from all the details of the items (1_ID, 2_ID) into a single row based on OrderID...
Where I get hungup is that the ItemID is different for each of the details so it creates a separate line item for each ItemID per OrderID.
I simply want to ignore if the ItemID matches. However The only way to join the tables is through the ItemID column.
Thanks again
Zane
June 30, 2010 at 12:15 pm
If you could post the Query you are attempting to use that might help us determine what you rproblem is without reinventing the wheel. I am not sure I understand what you mean by the ID might be different.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 30, 2010 at 12:48 pm
Here is my query. If I remove the full joins or use left, right I get no results.
SELECT DISTINCT o.ClientID,o.OrderID, i.ItemID, t1.1_ID,t2.2_ID
FROM Order as o
FULL JOIN Item as it ON i.OrderID = o.OrderID
FULL JOIN Table_1 as t1 ON t1.ItemID = i.ItemID
FULL JOIN Table_2 as t2 ON t2.ItemID = i.ItemID
WHERE o.ClientID = 10
Attached are some sample results.
As stated before I don't care about the ItemID not match because as you can see it is different. I just wan the OrderID to match and the values in 1_ID and 2_ID to be on the same line.
In the attached image, there are several null values because there are more than just 2 additional tables.
June 30, 2010 at 12:55 pm
In the attached image, there are several null values because there are more than just 2 additional tables.
I think you lost me here. what do you mean there are more than 2 additianl tables?
Also I would investigate why you have to do a full join. a left outer join would I think be in order if the recrord can return a null from on of the joins.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 30, 2010 at 1:01 pm
You are correct but even with LEFT OUTER JOIN it returns the same result of having each column on a different row.
June 30, 2010 at 1:21 pm
DECLARE @Orders TABLE (OrderID int, ClientID int)
INSERT INTO @Orders
SELECT 1, 234 UNION ALL
SELECT 2, 234 UNION ALL
SELECT 3, 234 UNION ALL
SELECT 4, 200
DECLARE @Items TABLE (ItemID int, OrderID int)
INSERT INTO @Items
SELECT 10, 1 UNION ALL
SELECT 11, 1 UNION ALL
SELECT 12, 2 UNION ALL
SELECT 13, 3
DECLARE @ItemType1 TABLE ([1_ID] int, ItemID int)
INSERT INTO @ItemType1
SELECT 1, 10 UNION ALL
SELECT 2, 12
DECLARE @ItemType2 TABLE ([2_ID] int, ItemID int)
INSERT INTO @ItemType2
SELECT 1, 11 UNION ALL
SELECT 2, 13
SELECT o.ClientID,
d.*
FROM@Orders o
INNER JOIN (
SELECT i.OrderID,
MIN([1_ID]) as [1_ID],
MIN([2_ID]) as [2_ID]
FROM@Items i
LEFT JOIN @ItemType1 t1 ON i.ItemID = t1.ItemID
LEFT JOIN @ItemType2 t2 ON i.ItemID = t2.ItemID
GROUP BY i.OrderID
) d ON O.OrderID = d.OrderID
June 30, 2010 at 2:43 pm
Works like a charm. Thank you!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply