Am I misunderstanding the outer join?

As I understand it, a left outer join should include records from the left table which match the where clauses for that table whether or not there are records from the right table that match the where clauses for that table.

CREATE TABLE dbo.Test1 (
testnumber varchar (10) NOT NULL
) ON PRIMARY
GO

CREATE TABLE dbo.Testlink (
testnumber varchar (10) NOT NULL ,
testdate datetime NOT NULL
) ON PRIMARY
GO

INSERT into dbo.Test1 values ('0051419')
GO

INSERT into dbo.Testlink values ('0051419','2004-11-22')
GO
INSERT into dbo.Testlink values ('0051419','2004-11-29')
GO
INSERT into dbo.Testlink values ('0051419','2004-12-22')
GO

select test1.testnumber, testlink.testdate
from dbo.test1 left outer join dbo.testlink on test1.testnumber=testlink.testnumber
where test1.testnumber='0051419'

returns, as expected,

testnumber testdate
---- ----------------
0051419 2004-11-22 00:00:00.000
0051419 2004-11-29 00:00:00.000
0051419 2004-12-22 00:00:00.000

(3 row(s) affected)

adding a where clause that results in no records matching in testlink however, results in no records being returned-but I expected the record from test1 being returned with a null value in testdate. Am I misunderstanding what should be happening? Or am I simply doing something wrong?

select test1.testnumber, testlink.testdate
from dbo.test1 left outer join dbo.testlink on test1.testnumber=testlink.testnumber
where test1.testnumber='0051419'
and testlink.testdate > '10/16/2006'

testnumber testdate
---- ----------------

(0 row(s) affected)

In the report I'm creating I do need to report the test1 records that meet the selection criteria even when there are no records in testlink that meet the selection criteria for that table.

Thanks.Yes - you are missing something. Everything you say is correct until you get to the bit about ALWAYS returning all rows from the left table that meet the where clause. You can think of the where clause being applied AFTER the join. You effectively want to apply the filter on testlink BEFORE the join. You can use a Derived Table or you can rely on a handy bit of syntax:
select test1.testnumber, testlink.testdate
from dbo.test1 left outer join dbo.testlink on test1.testnumber=testlink.testnumber
and testlink.testdate > '10/16/2006'
where test1.testnumber='0051419'
Putting the expression in the join syntax applies the filter before the left join is applied and should result in your required output.

HTH|||Hmmm. Thanks-that answers my question. It doesn't quite solve my problem but that's because I didn't sufficiently outline the circumstances-and I'm not sure the remaining issue is right for this forum. The remaining problem is that I'm using Crystal Reports & don't see an option for comparing a field to a constant value in defining the link (join). I'll go out to the Crystal site & see if I can get an answer to that question there.

Thanks a lot for explaining what I was missing.