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

Saxitalis
Saxitalis used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
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.
Brian CroweDatabase Administrator
Top Expert 2005

Commented:
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

Author

Commented:
Thanks man that was it!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial