Link to home
Start Free TrialLog in
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
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Russ Suter
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

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

I beg your pardon? What is wrong with the full join? Éric's answer works, but can get very cumbersome and might perform bad.
Avatar of 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?
Avatar of elucero

ASKER

yes please reopen so I can give points thanks
Avatar of elucero

ASKER

there you go  Qlemo.