I have two tables. Table A and Table B. Table A has column of just yearweek YrWk 200801 200802 200803 etc.. Table B has values of yearweek, acct#, usage YrWk Acct Usage 200801 1 15 200802 1 34 200803 1 22 200805 1 36 I want to combine the two table but notice in table B 200804 is missing. Because the acct did not report any usage. When I combine the two table I want 200804 to show a row which shows 0 for usage. I have tried a cross join but the dataset is so huge it will take too long to execute with a cartesian product outcome. Any idea how I can accomplish this without using a cross join? I tried a left join but they didnt work because I joined on yearweek so it just took the yearweeks that were listed.
Try an OUTER JOIN *edit* Hmm. I'm just running with the following code: declare @TableA TABLE (YrWk int) declare @TableB TABLE (YrWk int, Acct int, Usage int) insert into @TableA SELECT 200801 UNION SELECT 200802 UNION SELECT 200803 UNION SELECT 200804 UNION SELECT 200805 insert into @TableB SELECT 200801, 1, 15 union all select 200802, 1, 34 union all select 200803, 1, 22 union all select 200805, 1, 36 SELECT * FROM @TableA a FULL OUTER JOIN @TableB b ON a.YrWk = b.YrWk That provides me with the following results: YrWk YrWk Acct Usage 200801 200801 1 15 200802 200802 1 34 200803 200803 1 22 200804 NULL NULL NULL 200805 200805 1 36 So you have NULLs in rows where the "real" data is missing. What you could do is change your final `SELECT` to do the following: SELECT a.YrWk, ISNULL(b.YrWk, a.YrWk), ISNULL(b.Acct, 0), ISNULL(b.Usage, 0) FROM @TableA a FULL OUTER JOIN @TableB b ON a.YrWk = b.YrWk This returns the following: YrWk YrWk Acct Usage 200801 200801 1 15 200802 200802 1 34 200803 200803 1 22 200804 0 0 0 200805 200805 1 36
You can use a left outer join select ta.YrWk, tb.YrWk, tb.Acct, tb.Usage from TableA ta left outer join TableB tb on ta.YrWk=tb.YrWk But if you need to apply a filter on table b, you need to add it in the join clause or use is null select ta.YrWk, tb.YrWk, tb.Acct, tb.Usage from TableA ta left outer join TableB tb on ta.YrWk=tb.YrWk and tableB.colA=2 where tableB.somecol=1 or tableB.somecol is null