with ctedatename (date,name,id)
As
(select distinct t2.date,t1.name,t1.id from table1 as t1 cross join table2 as t2 )
select t3.date, t3.name as t1_name, ISNULL(t4.count,0) as Count from ctedatename as t3 left outer join table2 as t4 on t3.id=t4.t1_id and t3.date=t4.date
Test results
Quote:
date t1_name Count
2011-01-01 a 1
2011-01-01 b 2
2011-01-01 c 0
2011-01-01 d 0
2011-02-01 a 1
2011-02-01 b 0
2011-02-01 c 3
2011-02-01 d 0
2011-03-01 a 0
2011-03-01 b 0
2011-03-01 c 0
2011-03-01 d 1
SQL statements used to load test data
CREATE TABLE [dbo].[Table1](
[id] [int] NOT NULL,
[name] [nvarchar](30) NOT NULL
) ON [PRIMARY]
Go
CREATE TABLE [dbo].[Table2](
[id] [int] NOT NULL,
[t1_id] [int] NOT NULL,
[date] [date] NOT NULL,
[count] [int] NOT NULL
) ON [PRIMARY]
Go
insert into table1 (id,name) values(1,'a')
insert into table1 (id,name) values(2,'b')
insert into table1 (id,name) values(3,'c')
insert into table1 (id,name) values(4,'d')
insert into table2 (id,t1_id,date,count) values (1,1,'2011-01-01', 1)
insert into table2 (id,t1_id,date,count) values (2,2,'2011-01-01', 2)
insert into table2 (id,t1_id,date,count) values (3,1,'2011-02-01', 1)
insert into table2 (id,t1_id,date,count) values (4,3,'2011-02-01', 3)
insert into table2 (id,t1_id,date,count) values (5,4,'2011-03-01', 1)