Full outer join on one criteria, inner join on another

Question

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

Sample scripts

--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

I don't think Coalesce would be necessary as you allow inclusion of null values in your desired results. The solution can also be easily achieved with a left outer join as with Joe's solution. I do suspect, however, there is more to this. Are you able to provide more representative data or is Joe's solution satisfactory? — deutschZuid, Sep 01 '11 at 22:47
When you said, *'**Inner** join on another…'*, did you by any chance mean that the result set should *only* include orders that have details (even if for completely different `CurrencyID`)? — Andriy M, Sep 02 '11 at 12:36
When i said "`inner`" join i meant the `OrderID`, discarding rows that have no match (i.e. `inner join`). i also need to join, *when possible* by `currencyid` - keeping rows in both tables that have no matching partner (i.e. `full outer join`) — Ian Boyd, Sep 02 '11 at 13:39

score 2 · Accepted Answer · edited Mar 05 '20 at 17:45

2

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

edited Mar 05 '20 at 17:45

GreenGiant

4,930
1
46
76

answered Sep 01 '11 at 23:47

James

318
1
5

That seems to work. i think what was tripping me up is that i need to join Orders to other tables (e.g. `Users` for who created the order, or `o.OrderDate`). Instead i have to use these two tables joined as a derived table, and join **back** to `Orders` to get additional columns from the `Orders` table. But no, i had not tried that; but i have now. – Ian Boyd Sep 02 '11 at 13:50

score 0 · Answer 2 · answered Sep 01 '11 at 21:30

0

SELECT od.OrderID, od.CurrencyID, o.BuyAmount, o.BuyRate, od.SellAmount, od.SellRate
    FROM #OrderDetails od
        LEFT JOIN #Orders o
            ON od.OrderID = o.OrderID
                AND od.CurrencyID = o.CurrencyID

answered Sep 01 '11 at 21:30

Joe Stefanelli

132,803
19
237
235

This seems fine to me; although, given Ian mentioned right/full outer joins and coalesce, I'm wondering if the sample data doesn't cover all of the scenarios the SQL needs to handle. – James Sep 01 '11 at 22:20
@James: You are right. A case i *require* is when there is no matching *CurrencyID* between `Orders` and `OrderDetails`. If it makes it easier, it *may* be imposed that only one row exists in the Orders table for any given order (i.e. unique index on `Orders.OrderID`) Updated original question. – Ian Boyd Sep 01 '11 at 23:25

Full outer join on one criteria, inner join on another

Sample scripts

2 Answers2