October 29, 2009 at 6:53 am
How do I make only loanid distinct?
select 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
October 29, 2009 at 7:38 am
SELECT DISTINCT LoanId
-- Gianluca Sartori
October 29, 2009 at 7:49 am
That didn't work, here's the entire statment:
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
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, property P, borrower B, status s
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
I'm getting duplicate loanid's
October 29, 2009 at 7:54 am
That's because in the other tables you are joining you have the same loan multiple times.
If you don't need the columns in the joined tables, you can use a WHERE EXISTS subquery to test if the loan is present in the tables, otherwise you will always get duplicate records.
An alternative would be to group by loanid and use aggregate functions (MIN/MAX/AVG) on the other columns.
-- Gianluca Sartori
October 29, 2009 at 9:35 am
Okay..pretty sure I need the columns in the other tables
I have no idea how to do that can you give me an example?
October 29, 2009 at 9:39 am
OK, I need table(s) definition sample data and desired output.
Can you post it? See this article for more information:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
-- Gianluca Sartori
October 29, 2009 at 9:46 am
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
select 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, property P, borrower B, status s
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 distinct l.loanid from loan)
I was hoping the last line would work...but no
I'm not sure of the desired output...but if I can't figure this out I'll post the table def
October 29, 2009 at 9:50 am
You've posted on a 7,2000 , are you using that or a later version.
On 2005+ you can add a row_number() calculation and filter on that.
October 29, 2009 at 9:58 am
Sorry Dave you are correct I meant to be on the 2005
how do I do that calculation?
October 29, 2009 at 10:07 am
You will need to play with the order by if you are bothered by which row needs to be returned.
with cteloans
as
(
select 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,
row_number() over (partition by l.loanid order by l.loanid) as RowN
--FROM loan L, property P, borrower B, status s
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
)
select * from cteLoans where RowN = 1
October 29, 2009 at 10:11 am
Thanks Dave!!!
Thanks to the rest of you guys also!
Good karma to you!
October 29, 2009 at 10:13 am
Thankagain..
what if I need to add a where clause to the above query
Where l.branchoffice not in ('01','02')
October 29, 2009 at 4:48 pm
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply