I thought I knew enough SQL, but I am having problem with a left outer join.
I have an expense detail record that needs to link to a table by dept and account_code.
The query looks something like this:
select Detail.Spend, Budget.BudgetAmt
from detail left outer join budget on detail.dept = budget.dept AND dept.account_code = budget.account_code
This works great as long as there is a record that exactly matches the join conditions. But sometimes, there is no matching budget item. I want to get back the Detail.Spend from the details table with nulls for the budgetAmt. Instead, I don't get this record at all.
Isn't Left Outer Join supposed to return the left (detail) table when there is no match? Is there something different when multiple criteria are used as I do here?