question

sqlnewb avatar image
sqlnewb 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.
sql-server-2008t-sqljoinsouter-join
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
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.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlnewb avatar image sqlnewb commented ·
Outer Join didn't work. Still skipping over my week which I have no usage reported
0 Likes 0 ·
alexsdba avatar image
alexsdba answered
try this: select ta.YrWk, tb.YrWk, tb.Acct, tb.Usage from TableA ta full outer join TableB tb on ta.YrWk=tb.YrWk
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlnewb avatar image sqlnewb commented ·
I tried this it didn't work. Still skipping over my week in table B and not adding in a 0 for usage
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@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;
0 Likes 0 ·
sqlnewb avatar image sqlnewb commented ·
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
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
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
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlnewb avatar image sqlnewb commented ·
@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?
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.