SQL Server: left join results in fewer rows than left table

Question

I am using SQL Server (I believe it is 2005).

I have a TableA that has 2 columns and 439 rows (each row is unique).

 +----------+ |ID | Name | +----------+ 

I have a TableB that has 35 columns and many hundreds of thousands of rows (each row is also unique).

 +------------------------------------------------------------------------------+ |Date | ID | Name | Blah1 | Blah2 | ... | Hour1 | Hour2 | Hour3 | ... | Hour24 | +------------------------------------------------------------------------------+ 

Each row in TableB has hourly observations and some other house maintenance information. Now, for testing purposes, I'm only interested in today ie 4/19/2013.

If I do this:

 Select count(*) from TableB where Date = '4/19/2013 12:00:00 AM' 

I get 10526, which is correct, since there are 10526 different locations for which there is hourly observation data every day.

I want LEFT JOIN TableA and TableB on a.id = b.id , which on a.id = b.id produce a result with 439 rows.

Unfortunately, the result has 246 rows. How can it be? Does LEFT JOIN mean to return all rows in TableA regardless of whether there was a match in TableB ?

* EDIT *

The full query I used was:

 select * from TableA as a left join TableB as b on a.id = b.id where RealDate = '4/20/2013 12:00:00 AM' 

Answer 1

Try the following:

 select * from TableA as a left join (SELECT * from TableB where RealDate = '4/20/2013 12:00:00 AM') as b on a.id = b.id 

Or that:

 select * from TableA as a left join TableB as b on (a.id = b.id AND RealDate = '4/20/2013 12:00:00 AM')