Yet another Join Question

Question

I want to pull back information about a loan. One piece of information is a certain fee amount. If I simplify my query down to the loan number and fee amount, I still can not figure it out. The first query returns what I expect, one loan number and a 0 for the fee amount (fee was not applied) while the second one I can not get to work for love nor money. It will only return an empty results set no matter what type of join I try to apply. Using SQL Server 2005 if that makes a difference.
thanks for your time.

cheers bob

select 
  tblLoan.loanID as LoanNumber
 ,isnull(tblgfe1300.gfe1300feeTot,0) as RepairFunds
from
  tblLoan
LEFT JOIN tblgfe1300 on tblgfe1300.loanID = tblLoan.loanID AND tblgfe1300.gfe1300FeeName = 'Escrow Holdback'
WHERE tblLoan.loanID = '3250000167'

Now this is the one that does not work.

select 
  tblLoan.loanID as LoanNumber
  ,isnull(tblgfe1300.gfe1300feeTot,0) as RepairFunds
from
  tblLoan
  LEFT JOIN tblgfe1300 on  tblgfe1300.loanID = tblLoan.loanID
where
  tblLoan.loanID = '3250000167'
  AND tblgfe1300.gfe1300FeeName = 'Escrow Holdback'

Answer 1

I believe that in the table tblgfe1300, you have no record with the specified ID and the feename = "escrow holdback".

The reason the first query returns a result, is that your condition on the gfe1300FeeName collumn occurs 'at jointime', and since you're using a left join, all results will be contained in the resultset. Afterwards, the resultset is filtered on Id, and the gfe1300FeeName column is not taken into consideration anymore.

In the 2nd query, the 2nd column is taken into consideration after the join has been applied. So, if you have one record for the specified Id, but the value in the gfe1300FeeName column is not 'Escrow Holdback', that record will be left out of the resultset.

Answer 2

If the join has no effect you still have tbl.loanID in the result. But if you filter with WHERE then you have no results.

You can verify this by adding a WHERE tblgfe1300.gfe1300FeeName = 'Escrow Holdback' to the first query and then you won't get anything there either.

Answer 3

This query:

select L.loanID LoanNumber, 
   isnull(F.gfe1300feeTot, 0) RepairFunds
from tblLoan L 
   LEFT JOIN tblgfe1300 F
      on F.loanID = L.loanID
where L.loanID = '3250000167'  
    AND F.gfe1300FeeName = 'Escrow Holdback'

returns no rows, because the F.gfe1300FeeName expression is in the Where clause and not in the Join.

When using Outer Joins, The join condition is applied, (but no rows match), and then all the rows that are in the inner side of the outer join, but have no matching row in the table on the 'outer' side are added back in, but with NULL values for all the columns that should have come from the [non-existent] row in the outer table. Then, (because you are filtering on a column in this outer table tblgfe1300), your Where clause filters OUT those rows, because the value there (NULL) is not equal to 'Escrow Holdback'