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'
kowsika deviAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
SELECT * 
FROM Loln_LnDisbmntPaymntDet  p
WHERE p.lnno='GYDM2SP1708120005'
    AND NOT EXISTS (SELECT 1 FROM Lgen_HTHBankRevfeed_D_New n WHERE n.ProposalNo = p.Inno)
    AND NOT EXISTS (SELECT 1 FROM Loln_BulklnPay b WHERE b.Inno = p.Inno) 

Open in new window

0
 
Ryan ChongCommented:
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

0
 
kowsika deviAuthor Commented:
thanks vitor jj
0
 
kowsika deviAuthor Commented:
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]=''
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.