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
eluceroAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
This is a full outer join then.
select isnull(tbl1.date, tbl2.date), isnull(tbl1.region, tbl2.region), tbl1."tool cnt", tbl2."misc cnt"
from tbl1 full join tbl2 on tbl1.date = tbl2.date

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Russ SuterCommented:
I think you're looking for a union and a subquery. This should do the trick...
SELECT
	[tbl1and2].[date],
	[tbl1and2].[Region],
	SUM([tbl1and2].[tool cnt]) AS [tool cnt],
	SUM([tbl1and2].[misc cnt]) AS [misc cnt]
FROM
(
	SELECT
		[tbl1].[date],
		[tbl1].[Region],
		[tbl1].[tool cnt],
		NULL AS [misc cnt]
	FROM
	[dbo].[tbl1]
	UNION ALL
	SELECT
	[tbl2].[date],
	[tbl2].[Region],
	NULL AS [tool cnt],
	[tbl2].[misc cnt]
	FROM
	[dbo].[tbl2]
	) AS [tbl1and2]
GROUP BY
	[tbl1and2].[date],
	[tbl1and2].[Region]
ORDER BY
	[tbl1and2].[date]

Open in new window

1
Éric MoreauSenior .Net ConsultantCommented:
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

Open in new window

1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I beg your pardon? What is wrong with the full join? Éric's answer works, but can get very cumbersome and might perform bad.
1
eluceroAuthor Commented:
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?
0
eluceroAuthor Commented:
yes please reopen so I can give points thanks
0
eluceroAuthor Commented:
there you go  Qlemo.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.