duplicate record in one to many relationship

Mohan Arumugam

8y878SQL

How to prevent duplicate records in one too many relationships using SQL join query

create procedure loantype5

(@id int)

begin

select customer.Id,interest20.principalamount as principalamount,interest20.loantype as loantype,emi22.principalamount1 as principalamount1,emi22.loantype1 as loantype1 from customer

left join interest20 on customer.Id=interest20.customerid

left join emi22 on

where customer.Id=@id

group by

customer.Id,

interest20.principalamount,

interest20.loantype,

emi22.principalamount1,

emi22.loantype1

end

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mohan Arumugam8y ago
I tried a new way

create procedure loan8
(@id int)
as
begin
SELECT customer.Id, principalamount,loantype,principalamount1,loantype1 from customer
JOIN (
SELECT customerid, principalamount,loantype as loantype
FROM interest20
)as table2 ON customer.Id=table2.customerid
JOIN (
SELECT distinct customerid1, principalamount1 as principalamount1,loantype1 as loantype1
FROM emi22
) as table3 ON customer.Id=table3.customerid1
where customer.Id=@id
end
Mohan Arumugam8y ago
Hi JayaKumar

I can't match loan id.. because we have multiple loan for a customer.
we have 2 type of loan
1. Interest
2.Emi

once I insert a value means it displays it as 4 times. but in the insert table, it inserted only one time.

avoid duplicate value... if it empty it has to show null or empty
Jayakumar Balasubramaniam8y ago
Hi Mohan,

It depends on your columns and requirement and how you design the table.

There could be more than one loan for a customer so you may get duplicates, so add one more parameter in your procedure to get loan id and add that condition also in your join query.