I have two tables. Table A and Table B.
Table A has column of just yearweek
Table B has values of yearweek, acct#, usage
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.
asked Sep 21, 2011 at 10:33 AM in Default
You can use a left outer join
But if you need to apply a filter on table b, you need to add it in the join clause or use is null
try this: select ta.YrWk, tb.YrWk, tb.Acct, tb.Usage from TableA ta full outer join TableB tb on ta.YrWk=tb.YrWk
answered Sep 21, 2011 at 10:41 AM
Try an OUTER JOIN
Hmm. I'm just running with the following code:
That provides me with the following results:
So you have NULLs in rows where the "real" data is missing. What you could do is change your final
This returns the following: