Join Two tables with missing value

Question

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.

Answer 1

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

Answer 2

0

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 '11 at 10:41 AM

alexsdba
221 ● 1 ● 1 ● 4

I tried this it didn't work. Still skipping over my week in table B and not adding in a 0 for usage

Sep 21 '11 at 10:54 AM sqlnewb

@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;

Sep 21 '11 at 11:34 AM Oleg

I really do not understand why the full outer join is not working. It should list all the values from table A but it is skipping over the weeks which are not listed in table B

Sep 21 '11 at 12:01 PM sqlnewb

(comments are locked)

Answer 3

0

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

answered Sep 21 '11 at 11:10 AM

Håkan Winther
15k ● 29 ● 35 ● 46

@Hakan when you write:
tableB.colA=2 where tableB.somecol=1 or tableB.somecol is null What column am I suppose to reference? and Why are you putting the numbers 2 and 1?

Sep 21 '11 at 11:16 AM sqlnewb

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.

Sep 21 '11 at 11:57 AM Håkan Winther

(comments are locked)

Join Two tables with missing value

Follow this question

Related Questions