asked on

Record exist in one table but not in other 2 tables.

hi experts,
iam having 3 seperate tables . these 3 tables are interconnected with one key value. i want the record exists in this table Loln_LnDisbmntPaymntDet but not available in

this Lgen_HTHBankRevfeed_D_New and this Loln_BulklnPay table how to write query.

      select * from Loln_LnDisbmntPaymntDet where lnno='GYDM2SP1708120005'
      select * from Lgen_HTHBankRevfeed_D_New where ProposalNo ='GYDM2SP1708120005'
      select * from Loln_BulklnPay where lnno='GYDM2SP1708120005'

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ryan Chong

try use Left join like:

select a.*
from Loln_LnDisbmntPaymntDet a
left join (select * from Lgen_HTHBankRevfeed_D_New where ProposalNo ='GYDM2SP1708120005') b on a.key = b.key
left join (select * from Loln_BulklnPay  where lnno='GYDM2SP1708120005') c on a.key = c.key
where a.lnno='GYDM2SP1708120005'
and b.key is null
and c.key is null

Open in new window

kowsika devi

ASKER

thanks vitor jj

kowsika devi

ASKER

how can i implement in temp table jj

UPDATE #templndetNew set [INSTRUMENT UPDATION DETAILS]='Via DOL'
FROM #templndetNew T
join Loln_LnDisbmntPaymntDet p on t.[PROPOSAL NO] =p.lnno where
NOT EXISTS (SELECT 1 FROM Lgen_HTHBankRevfeed_D_New n WHERE n.ProposalNo = p.LnNo )
AND NOT EXISTS (SELECT 1 FROM Loln_BulklnPay b WHERE b.Lnno = p.lnno) and [INSTRUMENT UPDATION DETAILS]=''

Vitor Montalvão

UPDATE T set [INSTRUMENT UPDATION DETAILS]='Via DOL'
       FROM   #templndetNew T                               
join Loln_LnDisbmntPaymntDet  p on t.[PROPOSAL NO] =p.lnno 
where  NOT EXISTS (SELECT 1 FROM Lgen_HTHBankRevfeed_D_New n WHERE n.ProposalNo = p.LnNo )
    AND NOT EXISTS (SELECT 1 FROM Loln_BulklnPay b WHERE b.Lnno = p.lnno)
    AND t.[INSTRUMENT UPDATION DETAILS]=''

Open in new window