We help IT Professionals succeed at work.

sql ? on joins

101 Views
Last Modified: 2015-09-30
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
Comment
Watch Question

"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Russ SuterSenior Software Developer
CERTIFIED EXPERT

Commented:
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 MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
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"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
I beg your pardon? What is wrong with the full join? Éric's answer works, but can get very cumbersome and might perform bad.

Author

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?

Author

Commented:
yes please reopen so I can give points thanks

Author

Commented:
there you go  Qlemo.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.