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!

Microsoft SQL ServerMicrosoft SQL Server 2008Databases

Last Comment

John Porter

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

Jim Horn

1/22/2016

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Brian Crowe

1/22/2016

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

John Porter

1/23/2016

ASKER

Thanks man that was it!!

Your help has saved me hundreds of hours of internet surfing.

fblack61