Table_A
TxID RelatedTxID
-------------------
1 NULL
2 NULL
3 1
Table_B
OrderID TxID OrderDescription
-----------------------------------
1 开发者_JAVA百科 1 Description_1
2 2 Description_2
I want to get an output which will give me order description for the transaction. But if the transaction does not have an order description I want to display it's related transaction's order description (Related transaction will always have an order description)
Output
TxID RelatedTxID OrderDescription
------------------------------------
1 NULL Description_1
2 NULL Description_2
3 1 Description_1
I am thinking of something like below but stuck at what should come in the ISNULL expression.
select
a.TxID,
a.RelatedTxID,
ISNULL(b.OrderDescription, <<get its related transaction's order description>>)
from Table_A a
left outer join Table_B b
on a.TxID = b.TxID
Thanks
select
a.TxID,
a.RelatedTxID,
ISNULL(b1.OrderDescription, b2.OrderDescription)
from Table_A a
left outer join Table_B b1 on a.TxID = b1.TxID
left outer join Table_B b2 on a.RelatedTxID = b2.TxID
精彩评论