How to keep all records when join two tables and check condition 1

[kate8]

Hi All,

I left join two tables, need keep all the records in first table, but also need check the the date field in second table if the date field is not null. Since some records in first table are not in the second table,those records have no date field, when I check the date field, those records which are not in the second table don't show the the result even I did left join. How can I keep the all the records in the first table and also can check the date.
When I do this, I got all the records:

select Number,Name, Source, Startdate,Enddate
from Test1
left outer join Test2 ON Test1.Number = Test2.Number

But when I check the date, those records which are not in the second table don't show.

select Number,Name, Source, Startdate,Enddate
from Test1
left outer join Test2 ON Test1.Number = Test2.Number
where
Startdate <= '2013-11-01' and Enddate > '2013-12-01'

Thank you so much for any helps!!!

 

[gmmastros]

You should put the condition in the ON clause, like this:

select Number,Name, Source, Startdate,Enddate 
from   Test1
       left outer join Test2 
          ON  Test1.Number = Test2.Number
          And Startdate <= '2013-11-01' 
          and Enddate > '2013-12-01'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kate8]

Hi George,

Thank you so much!!! It works perfectly.

 

[gmmastros]

Do you understand it? Do you want me to explain it?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kate8]

Thanks George!!
Yeah, I checked it and found out the differences.