Avatar of elucero
elucero
 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
Microsoft SQL Server 2008Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
elucero

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Qlemo

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Russ Suter

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

Éric Moreau

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

Qlemo

I beg your pardon? What is wrong with the full join? Éric's answer works, but can get very cumbersome and might perform bad.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
elucero

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?
elucero

ASKER
yes please reopen so I can give points thanks
elucero

ASKER
there you go  Qlemo.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.