question

Dan beadle avatar image
Dan beadle asked

SQL left outer join on two fields

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?

Thanks

sql-server-2005
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Squirrel 1 avatar image
Squirrel 1 answered

the query should do just as what you said. Unless this is not the complete query.

  1. Post the full query
  2. Post some sample data to illustrate the situation
  3. Post the expected result
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dan beadle avatar image
Dan beadle answered

Squirrel - thanks for the help. I got to the bottom of this shortly after posting. It was a case of focusing on the wrong problem. The excerpt was incomplete - I had abstracted it to the above from an almost two page query. Since I had just changed an inner join to left outer join, I focused on the latest change. It didn't work as expected. The root cause was a "having" clause on the right table. When the left outer join completed OK, returning a set with null values in the right table for missing records, the HAVING clause failed on the right table clause Budget.Date = @Today.

THanks for the quick help - if only to confirm I was on the wrong track.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.