October 29, 2009 at 10:02 am
I have four tables, that are joined together by one column called loanid.
I have been asked to join the four tables and bring back everything in all four tables..but loanid must be distinct (no duplicates).
Here's the query:
select distinct (l.loanid), l.branchoffice, l.AcquiredFrom,
l.previouslnnum, b.lastname, b.firstmiddlename,
p.addressline1, p.addressline2, p.city,
p.state, p.zip, l.originalamt,
s.primstat, p.appraisalvalue, l.glplan
FROM loan L
INNER JOIN borrower B ON B.LoanID = L.LoanID
INNER JOIN property P ON P.LoanID = L.LoanID AND P.LoanID = B.LoanID
INNER JOIN status S ON S.LoanID = L.LoanID AND S.LoanID = B.LoanID AND S.LoanID = P.LoanID
where l.loanid in (select l.loanid from loan group by l.loanid)
As you can see I've tried I few different ways, but can't get loanid to stop being duplicated.
October 29, 2009 at 10:13 am
My guess is it's the data you're returning.
If you have a loan in a table, say borrower, and you have two borrowers, what do you want done? Only the first one returned? If you examine your data, my guess is that all the stuff you are pulling back does not fit into one row.
If it does, then please post some sample data and DDL .
October 29, 2009 at 10:17 am
your DB Design and how they related should tell you the answer.
Does all the columns in your select query (apart from loanid) will occur only once for every loanid?
if not, you will tend to get the duplicates...(Logically correct also)
Regards
Jus
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply