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

Question

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

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

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

I have TableB that has 35 columns and many hundreds of thousand 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 keeping information. Now for testing purposes I am only interested in today's date i.e 4/19/2013.

If I do:

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

I get 10526, which is correct as there are 10526 distinct locations for which there is hourly observation data each day.

I want to LEFT JOIN TableA and TableB on a.id = b.id, which SHOULD produce a result that has 439 rows.

Unfortunately, the result has 246 rows. How can this be? Isn't a LEFT JOIN suppose to return all rows in TableA regardless of whether there was a match in TableB?

*EDIT*

The complete 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 this:

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 this:

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