x

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.

more ▼

asked Sep 21 '11 at 10:33 AM in Default

sqlnewb gravatar image

sqlnewb
216 26 30 31

can you post your query? you might have something going on in your query that's different that some of the demo queries on the answers.

Posting table create and insert scripts to create an entire reproduction scenario like in @ThomasRusthon 's answer would be helpful.
Sep 21 '11 at 07:39 PM KenJ
(comments are locked)
10|1200 characters needed characters left

4 answers: sort newest

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
more ▼

answered Sep 21 '11 at 11:10 AM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

@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)
10|1200 characters needed characters left
try this: select ta.YrWk, tb.YrWk, tb.Acct, tb.Usage from TableA ta full outer join TableB tb on ta.YrWk=tb.YrWk
more ▼

answered Sep 21 '11 at 10:41 AM

alexsdba gravatar image

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)
10|1200 characters needed characters left

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
more ▼

answered Sep 21 '11 at 10:34 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.4k 14 20 44

Outer Join didn't work. Still skipping over my week which I have no usage reported
Sep 21 '11 at 11:06 AM sqlnewb
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x977
x111
x11

asked: Sep 21 '11 at 10:33 AM

Seen: 1613 times

Last Updated: Sep 21 '11 at 10:33 AM