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
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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]
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.
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.