Outer Join Problems

[claudermilk]

I'm completely out of ideas on this one & it seems like it should be easy. The data I am looking at is:

Table #1 Salesperson
salesperson ID

Table #2 Sales order
date
sales order number
salesperson ID

the join is: Left Outer Join salesperson on salesperson.id = sales order.id

This results in:

salesperson sales order
A 1
A 2
B 3
C 4
D 5

I then want to apply a date filter to look at YTD only and if order 4 is from last year, I expect to see:

salesperson sales order
A 1
A 2
B 3
C null
D 5

but what I get is:

salesperson sales order
A 1
A 2
B 3
D 5


This example is a cut-down query I'm using to test for a much more complex set of unioned saved views. I would expect once this test set works I can transfer the same logic over.

 

[vongrunt]

Where you placed date filter? In WHERE clause or LEFT OUTER JOIN clause?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[claudermilk]

It's in the where clause. To complicate things, I am grabbing the last 3 years' worth of data, then in Crystal Reports knocking it down to one year. I'm hoping this won't cause more problems; it seems that filtering is internal to the Crystal app.

The SQL looks like this:

Where Year(date) = Year(GetDate()) - 2

 

[vongrunt]

Move this condition to LEFT OUTER JOIN:

 Left Outer Join salesperson on salesperson.id = sales 
	[b]AND Year(date) = Year(GetDate()) - 2[/b]

What happens?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[claudermilk]

I get a whole bunch of null salesperson.id and still no results of the id with a null sales order. It seems to be exactly the opposite of what I'm looking for.

I'm feeling much like your signature emoticon right now.

 

[claudermilk]

Hold that thought. I went back & tried from scratch. THe exact SQL tried was:

SELECT dbo.SalSalesperson.Salesperson, 
             dbo.SorMaster.SalesOrder, 
             dbo.SorMaster.EntrySystemDate
FROM         dbo.SalSalesperson LEFT OUTER JOIN
             dbo.SorMaster ON dbo.SalSalesperson.Salesperson = dbo.SorMaster.Salesperson and Year(dbo.SorMaster.EntrySystemDate) = Year(GetDate())
Order BY dbo.SalSalesperson.Salesperson

and it did work! Now, in the actual query I will be using there's a few other where clauses I need to use--do those all have to be within the Left outer join clause?

 

[claudermilk]

OK, taking that and playing with it a bit in Enterprise Manager and Query Analyzer, I now have the real query working as expected! For whatever reason, Enterprise Manager switched join to a Right Outer and moved that join with the contstraints to the end; now it works. In the end lookng at it, it is indeed stupidly simple.

Thanks for the help. Now to make sure I can get all the queries working.