Join two SQL tables and populate Date field on all records SQL Server 2008

Hello Experts,

I have two tables: MaterialT_1 and MaterialT_2

MaterialT_1:
Date            M1            Grp1            Grp2        Grp3
11/29/2015      10            AA            BA            CA

Material_2:
Date            M2             Grp1            Grp2        Grp3
11/29/2015      2            AA            BA            CA
11/29/2015       4            AB             BC             CC            
11/30/2015      15            AB            BC            CC      

I have joined then in a view like this:

I would like to join these two tables in a view so the results look like this:

Date               M1             M2            Grp1            Grp2        Grp3
11/29/2015      10            2            AA            BA            CA
11/29/2015      0            4            AB            BC            CC      
11/30/2015      0             15            AB            BC            CC

Does anyone know how to do this?

Thanks!
SaxitalisAsked:
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.

Jim HornSQL Server Data DudeCommented:
Give this a whirl...
SELECT [Date], Sum(M1) as M1, Sum(M2) as M2, Grp1, Grp2, Grp3
FROM (
   SELECT [Date], M1, 0 as M2, Grp1, Grp2, Grp3
   FROM MaterialT_1
   UNION ALL
   SELECT [Date], 0, M2, Grp1, Grp2, Grp3
   FROM Material_2) a
GROUP BY [Date], Grp1, Grp2, Grp3
]ORDER BY [Date]

Open in new window

Thoughts
The intent is to group on Date, Grp1, Grp2, Grp3.  If there is GrpX logic then you'll have to explain that.
You'll have to explain the ordering, as it looks like date first, then M1 DESC, then M2 ASC.
Should name the date column something other then Date so that square brackets can be avoided.

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
Brian CroweDatabase AdministratorCommented:
You didn't provide any logic just output so here's a guess.

DECLARE @MaterialT_1 TABLE
(
	Date		DATE,
	M1			INT,
	Grp1		VARCHAR(10),
	Grp2		VARCHAR(10),
	Grp3		VARCHAR(10)
);

DECLARE @Material_2 TABLE
(
	Date		DATE,
	M2			INT,
	Grp1		VARCHAR(10),
	Grp2		VARCHAR(10),
	Grp3		VARCHAR(10)
);

INSERT @MaterialT_1 (Date, M1, Grp1, Grp2, Grp3)
VALUES ('20151129', 10, 'AA', 'BA', 'CA');

INSERT @Material_2 (Date, M2, Grp1, Grp2, Grp3)
VALUES ('20151129', 2, 'AA', 'BA', 'CA'),
	('20151129', 4, 'AB', 'BC', 'CC'),
	('20151130', 15, 'AB', 'BC', 'CC');   

SELECT T2.Date, ISNULL(T1.M1, 0) AS M1, T2.M2, T2.Grp1, T2.Grp2, T2.Grp3
FROM @Material_2 AS T2
LEFT OUTER JOIN @MaterialT_1 AS T1
	ON T1.Date = T2.Date
	AND T1.Grp1 = T2.Grp1
	AND T1.Grp2 = T2.Grp2
	AND T1.Grp3 = T2.Grp3

Open in new window

SaxitalisAuthor Commented:
Thanks man that was it!!
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

From novice to tech pro — start learning today.