asked
Join Two tables with missing value
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
try this: select ta.YrWk, tb.YrWk, tb.Acct, tb.Usage from TableA ta full outer join TableB tb on ta.YrWk=tb.YrWk
@sqlnewb This does not appear to be possible, the full outer join should do the trick. Just make sure that you always display the YrWk value regardless whether it exists in both or just one of the tables. In other words, try this:
select
isnull(ta.YrWk, tb.YrWk) YrWk,
tb.Acct, isnull(tb.Usage, 0) Usage
from TableA ta full outer join TableB tb
on ta.YrWk = tb.YrWk; What do you want to do with account column? As is, it will show up in the results as NULL (for not reported weeks) and if you need to display the misssing acount numbers instead of diplaying null then you need to include an Acct column from the Account parent table in the mix, something like this (assuming that you do have a parent account table which has Acct column with unique Acct value in every record). You can then afford go with left join :)
select
ta.YrWk, ac.Acct, isnull(tb.Usage, 0) Usage
from TableA ta cross join Account ac
left join TableB tb
on ta.YrWk = tb.YrWk
and ac.Acct = tb.Acct
order by 2, 1;
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
It was just an example, I wanted to demonstrate how you need to write any filter for table2 to avoid reducing the final result from tableA.
