Link to home
Create AccountLog in
Avatar of John Porter
John PorterFlag for United States of America

asked on

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!
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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

Avatar of John Porter

ASKER

Thanks man that was it!!