Menu
i need to join Header and Detail rows into one resultset:
(sample DDL and inserts to follow):
Orders:
OrderID OrderDate CurrencyID BuyAmount BuyRate
======= ======================= ========== ========= ========
1 2011-09-01 15:57:00.000 7 12173.60 1.243893
1 2011-09-01 15:57:00.000 9 69.48 1
OrderDetails:
OrderID CurrencyID SellAmount SellRate
======= ========== ========== ========
1 7 10000 1
1 8 12384 0.9638
i want them joined on OrderID and CurrencyID:
OrderID CurrencyID BuyAmount BuyRate SellAmount SellRate
======= ========== ========= ======== ========== ========
1 7 12173.60 1.243893 10000 1
1 8 NULL NULL 12384 0.9638
1 9 69.48 1 NULL NULL
--USE Scratch
--Create a temporary `Orders` and, `OrderDetails` tables:
IF OBJECT_ID('tempdb..#Orders') > 0 DROP TABLE #Orders
CREATE TABLE #Orders
(
OrderID int NOT NULL,
OrderDate datetime NOT NULL,
CurrencyID int NOT NULL,
BuyAmount money NOT NULL,
BuyRate real NOT NULL
)
IF OBJECT_ID('tempdb..#OrderDetails') > 0 DROP TABLE #OrderDetails
CREATE TABLE #OrderDetails
(
OrderID int NOT NULL,
CurrencyID int NOT NULL,
SellAmount money NOT NULL,
SellRate real NOT NULL
)
-- **Insert sample data:**
INSERT INTO #Orders (OrderID, OrderDate, CurrencyID, BuyAmount, BuyRate)
VALUES (1, '20110901 15:57:00', 7, 12173.60, 1.2438933)
INSERT INTO #Orders (OrderID, OrderDate, CurrencyID, BuyAmount, BuyRate)
VALUES (1, '20110901 15:57:00', 9, 69.48, 1)
INSERT INTO #OrderDetails (OrderID, CurrencyID, SellAmount, SellRate)
VALUES (1, 7, 10000, 1)
INSERT INTO #OrderDetails (OrderID, CurrencyID, SellAmount, SellRate)
VALUES (1, 8, 12384, 0.9638)
/*Desired Output:
OrderID CurrencyID BuyAmount BuyRate SellAmount SellRate
======= ========== ========= ======== ========== ========
1 7 12173.60 1.243893 10000 1
1 8 NULL NULL 12384 0.9638
1 9 69.48 1 NULL NULL
*/
i can't find a combination of RIGHT OUTER JOIN, FULL OUTER JOIN, COALESCE that can produce my desired output.
Update:
It's also possible that OrderDetails doesn't contain a matching CurrencyID from the Orders table:
Orders:
OrderID CurrencyID BuyAmount BuyRate
======= ========== ========= ========
1 7 12173.60 1.243893
1 9 69.48 1
OrderDetails:
OrderID CurrencyID SellAmount SellRate
======= ========== ========== ========
1 8 12384 0.9638
879
You either have to use two LEFT joins and filter out the records you don't want, or alternatively use a View to scope the INNER JOIN. Save this answer. Show activity on this post. Yes you can do both is the same query, and yes the order is important.
For SQL Server, CROSS JOIN and FULL OUTER JOIN are different. CROSS JOIN is simply Cartesian Product of two tables, irrespective of any filter criteria or any condition. FULL OUTER JOIN gives unique result set of LEFT OUTER JOIN and RIGHT OUTER JOIN of two tables. It also needs ON clause to map two columns of tables.
No, they are not. Full outer join returns common records in both the tables + uncommon records for left table + uncommon records from right table. Where as in Cross Join we will get Cartesian product, that means every record of left table will be combined with every row in right table and result was returned back.
for joining two tables, we require 1 join statement and for joining 3 tables we need 2 join statements.
So, you've tried this?
SELECT
COALESCE(o.OrderID, od.OrderID) AS OrderID,
COALESCE(o.CurrencyID, od.CurrencyID) AS CurrencyID,
o.BuyAmount,
o.BuyRate,
od.SellAmount,
od.SellRate
FROM
#Orders AS o
FULL OUTER JOIN #OrderDetails AS od
ON o.OrderID = od.OrderID
AND o.CurrencyID = od.CurrencyID
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
