Invoice table

Reconcile table

Link: Invoice.ObjType = Reconcile.SrcObjType and Invoice.DocEntry = Reconcile.SrcObjEntry

Expected result:

I am trying to union the reconcile line that is not link directly to invoice but it is in the same ReconNo with reconciled invoice line. Is this possible in SQL?

Thanks for your help

CodePudding user response：

Maybe this will get you there?

with Invoice  (DocEntry,    ObjType,    Amount) as(
select 11,  13, 200 
),
Reconcile  (ReconNo,Line,SrcObjType,SrcObjEntry,Amount) as(
select 1,1,24,15,150  union all
select 1,2,13,11,150 
),
connection as (
select DocEntry,ReconNo
from Invoice as i
join Reconcile as r on i.DocEntry=r.SrcObjEntry
)
select * from Invoice union all
select SrcObjEntry as DocEntry, SrcObjType as ObjType,Amount
from connection c
join Reconcile r on r.ReconNo = c.ReconNo
where r.SrcObjEntry not in (select DocEntry from invoice)