September 10, 2015 at 9:00 am
In some cases there happens to be 2 people on the same loan (loanID) distinguished by borrowerID (1 &2) they share the same address and everything else. I wanted to see if we can improve the subquery.
SELECT
L.Loanid
,B.FirstMiddleName AS First1
,B.LastName AS Last1
,(SELECT B.FirstMiddleName
FROM Borrower B
LEFT JOIN Status As S on S.LoanID = B.LoanID
WHERE S.PrimStat = '1' and B.Deceased = '0'
AND B.BorrowerID = '2' and L.LoanID = B.LoanID ) AS First2
,(SELECT B.LastName
FROM Borrower B
LEFT JOIN Status As S on S.LoanID = B.LoanID
WHERE S.PrimStat = '1' and B.Deceased = '0'
AND B.BorrowerID = '2' and L.LoanID = B.LoanID ) AS Last2
,MA.AddressLine1
,MA.AddressLine2
,MA.City
,MA.State
,MA.Zip
FROM Loan L
LEFT JOIN Status As S on S.LoanID = L.LoanID
LEFT JOIN Borrower B on B.LoanID = L.LoanID
LEFT JOIN MailingAddress MA on MA.LoanID = L.LoanID
WHERE S.PrimStat = '1' and B.Deceased = '0' and B.BorrowerID = '1'
September 10, 2015 at 9:08 am
It doesn't look like you need the subqueries at all?
SELECT
L.Loanid
,B.FirstMiddleName AS First1
,B.LastName AS Last1
,B_TWO.FirstName AS First2
,B_TWO.LastName AS Last2
,MA.AddressLine1
,MA.AddressLine2
,MA.City
,MA.State
,MA.Zip
FROM Loan L
LEFT JOIN Status As S on S.LoanID = L.LoanID
LEFT JOIN Borrower B on B.LoanID = L.LoanID and B.Deceased = '0' and B.BorrowerID = '1'
LEFT JOIN MailingAddress MA on MA.LoanID = L.LoanID
LEFT OUTER JOIN Borrower B_TWO on B_TWO.LoanID = L.LoanID and B_TWO.Deceased = '0' and B_TWO.BorrowerID = '2'
WHERE S.PrimStat = '1'
September 10, 2015 at 9:32 am
Your WHERE clauses are converting your LEFT JOINs on Status and Borrower into INNER JOINs.
September 10, 2015 at 9:40 am
Thank you Luis and Zzartin,
I wasn't thinking about the self joins. And no need of Left joins with where clause..
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply