how cani build this critical query

[andypower]

i am developing a bank application in vb 6 and sql server.
i have 2 tables. one is LoanPayment and Other is LoanReceived
when bank gives any loan to customer it is saved in LoanPayment Table with its name and deal no{deal no changes from customer to customer i.e suppose Mr. XYZ has taken 3 loans [Rs.5000 , Rs.10000, Rs. 25000]from bank then his deal no r 1 for 5000 and 2 for 10000, and 3 for 25000] and Mr. LML has taken only 2 loans[Rs.15000, Rs.35000] from bank then his deal no 1 for 15000 and 2 for 35000 i. e deal r changes from customer to customer}
when customer return his loan then it is saved in LoanReceived Table with respective deal no and return amount.{if Mr. XYZ return Rs.250 against his deal no 1 then it is stores as name-xyz, deal no-1 and principlepaid-Rs.250}
now i want to find those name who do have any single entry in LoanReceived Table i.e those deal of clients against which client do not paid any single amount.
i used not in but i cant write it properly.
so plz help in writting this query.

 

[cbsm]

OK ...
I suppose you meant
"who do NOT have any single entry in LoanReceived"

LoanPayment
Name / Deal_No / Amount

LoanReceived
Name / Deal_No / Amount


select * from loanPayment lp where lp.deal_no not in (select lr.deal_no from loanReceived lr)

This will select you all records from the loanPayment table that do not have a corresponding record in the loanreceived table.

I assumed than Deal_No is unique in loanPayment.
I am not sure about this from your explanation. If this is not the case I strongly recomend you to add a unique key in the loanpayment table (and use this key for the not in list).
Another (bad) way would be to combine Name & deal_no (if this is unique) like this for example :
select * from loanPayment lp where lp.deal_no & lp.name not in (select lr.deal_no & lr.name from loanReceived lr)

 

[BogdanMBM]

Does dis work for you? (if I understood the question):

select * from LoanPayment LP where
not exists
(select * from LoanReceived LR
where LP.DealNomber = LR.DealNomber and
LP.Name = LR.Name)

Hope I've been helpful,
Bogdan Muresan.