asked on
sql ? on joins
Hi, I have 2 tbls . I need it to look like the results below. Notice on tbl1 there is no 9.3.15 data and on tbl 2 there is not 9.4.15 data.
tbl1 tbl2
date Region tool cnt date Region misc cnt
9/1/2015 south 4 9/1/2015 south 12
9/2/2015 north 5 9/2/2015 north 14
9/4/2015 east 6 9/3/2015 west 35
9/5/2015 west 7
results
date region tool cnt misc cnt
9/1/2015 south 4 12
9/2/2015 north 5 14
9/3/2015 east null 35
9/4/2015 west 6 null
9/5/2015 west 7 null
tbl1 tbl2
date Region tool cnt date Region misc cnt
9/1/2015 south 4 9/1/2015 south 12
9/2/2015 north 5 9/2/2015 north 14
9/4/2015 east 6 9/3/2015 west 35
9/5/2015 west 7
results
date region tool cnt misc cnt
9/1/2015 south 4 12
9/2/2015 north 5 14
9/3/2015 east null 35
9/4/2015 west 6 null
9/5/2015 west 7 null
Microsoft SQL Server 2008Microsoft SQL ServerMicrosoft SQL Server 2005
Last Comment
elucero
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
try this:
select D.Date, isnull(t1.region, t2.region) as region, t1.[tool cnt], t2.[misc cnt]
from (
select date from tbl1
UNION
select date from tbl2
) AS D
left join tbl1 as t1
on t1.date = D.date
left join tbl2 as t2
on t2.Date = D.Date
I beg your pardon? What is wrong with the full join? Éric's answer works, but can get very cumbersome and might perform bad.
ASKER
sorry it's right the full join. I wanted to give all points, but I accidently hit the later 2 first, then I couldn't give it to you. Is there a way I can go back and give you points?
ASKER
yes please reopen so I can give points thanks
ASKER
there you go Qlemo.
Open in new window