MS SQL Joins, Union, combined queries from 2 tables in one view 1

[rootl]

Greetings,

I have 2 tables: "ThreeFloors" and "FourthFloor". I have 3 queries which work well individually. One of the queries combines rows from both tables where Date and Hour columns match. Another query shows only rows from the ThreeFloors, and the third query shows only rows from the FourthFloor.

I need to know how to combine them so all results will show in one view with no duplicates. I have attached a screen capture that shows what I will need. Blank fields are null.

I believe I need an outer join combined with an inner join.

Below is my schema and also the queries. Thanks.

CREATE TABLE ThreeFloors
(
ID int identity(1,1) not null primary key,
Date datetime,
Hour varchar(50),
FirstFloor int,
MainFloor int,
ThirdFloor int,
TotalThreeFloors int,
FourthFloorID int,
TotalThreeFloorsPlusFourthFloor int,
Combined varchar(50),
);


CREATE TABLE FourthFloor
(
FourthFloorID int identity(1,1) not null primary key,
DateFourthFloor datetime,
HourFourthFloor varchar(50),
FourthFloor int
);


ALTER TABLE ThreeFloors ADD CONSTRAINT FK_ThreeFloors_FourthFloor FOREIGN KEY(FourthFloorID)
REFERENCES FourthFloor (FourthFloorID);

ALTER TABLE ThreeFloors CHECK CONSTRAINT FK_ThreeFloors_FourthFloor;



INSERT INTO ThreeFloors
(Date, Hour, FirstFloor, MainFloor, ThirdFloor,TotalThreeFloors, Combined)
VALUES
('5/8/2014 12:00:00 AM', '3PM','102','78','118','298', 'Yes'),
('5/8/2014 12:00:00 AM', '10AM','30','48','199','277', 'No'),
('5/7/2014 12:00:00 AM', '1PM','93','98','66','257', 'Yes'),
('5/7/2014 12:00:00 AM', '12PM','84','78','89','251', 'Yes'),
('5/7/2014 12:00:00 AM', '11AM','34','44','233','311', 'No'),
('5/7/2014 12:00:00 AM', '10AM','65','101','99','265', 'Yes'),
('5/6/2014 12:00:00 AM', '12PM','88','159','123','370', 'Yes'),
('5/6/2014 12:00:00 AM', '9AM','265','112','49','426', 'No'),
('5/6/2014 12:00:00 AM', '8AM','234','97','154','485', 'No')
;


INSERT INTO FourthFloor
(DateFourthFloor, HourFourthFloor, FourthFloor)
VALUES
('5/8/2014 12:00:00 AM', '3PM','78'),
('5/7/2014 12:00:00 AM', '2PM','42'),
('5/7/2014 12:00:00 AM', '1PM','24'),
('5/7/2014 12:00:00 AM', '12PM','53'),
('5/7/2014 12:00:00 AM', '10AM','19'),
('5/6/2014 12:00:00 AM', '4PM','68'),
('5/6/2014 12:00:00 AM', '3PM','24'),
('5/6/2014 12:00:00 AM', '12PM','59'),
('5/6/2014 12:00:00 AM', '12AM','74')
;



Query 1 shows rows where Date and Hour match:

SELECT h.ID,f.FourthFloorID, h.Date, h.Hour, h.FirstFloor, h.MainFloor, h.ThirdFloor, h.TotalThreeFloors, f.FourthFloor, (h.TotalThreeFloors + f.FourthFloor) AS TotalThreeFloorsPlusFourthFloor, h.Combined
FROM ThreeFloors h
INNER JOIN FourthFloor f ON h.Date=f.DateFourthFloor WHERE h.Hour=f.HourFourthFloor;



Query 2 shows all rows from FourthFloor

SELECT f.FourthFloorID, f.DateFourthFloor, f.HourFourthFloor, f.FourthFloor
FROM FourthFloor f;



Query 2 shows all rows from ThreeFloors:

SELECT h.ID, h.Date, h.Hour, h.FirstFloor, h.MainFloor, h.ThirdFloor, h.TotalThreeFloors, h.Combined
FROM ThreeFloors h;

 

[gk53]

try that

select distinct *
	from (
SELECT h.ID,f.FourthFloorID, 
	h.Date, 
	h.Hour, 
	f.DateFourthFloor,
	f.HourFourthFloor,
	h.FirstFloor, 
	h.MainFloor, 
	h.ThirdFloor, 
	h.TotalThreeFloors, 
	f.FourthFloor, 
	(h.TotalThreeFloors + f.FourthFloor) AS TotalThreeFloorsPlusFourthFloor
	FROM ThreeFloors h
	left JOIN FourthFloor f 
		ON h.Date=f.DateFourthFloor 
		and h.Hour=f.HourFourthFloor
union
SELECT h.ID,f.FourthFloorID, 
	h.Date, 
	h.Hour, 
	f.DateFourthFloor,
	f.HourFourthFloor,
	h.FirstFloor, 
	h.MainFloor, 
	h.ThirdFloor, 
	h.TotalThreeFloors, 
	f.FourthFloor, 
	(h.TotalThreeFloors + f.FourthFloor) AS TotalThreeFloorsPlusFourthFloor
	FROM ThreeFloors h
	right JOIN FourthFloor f 
		ON h.Date=f.DateFourthFloor 
		and h.Hour=f.HourFourthFloor
) j

 

[gk53]

or this one if you need to order properly

select *
	from (select *
		from (SELECT h.ID,f.FourthFloorID, 
					h.Date, 
					h.Hour, 
					f.DateFourthFloor,
					f.HourFourthFloor,
					h.FirstFloor, 
					h.MainFloor, 
					h.ThirdFloor, 
					h.TotalThreeFloors, 
					f.FourthFloor, 
					(h.TotalThreeFloors + f.FourthFloor) AS TotalThreeFloorsPlusFourthFloor
					FROM ThreeFloors h
					left JOIN FourthFloor f 
						ON h.Date=f.DateFourthFloor 
						and h.Hour=f.HourFourthFloor
				union
				SELECT h.ID,f.FourthFloorID, 
					h.Date, 
					h.Hour, 
					f.DateFourthFloor,
					f.HourFourthFloor,
					h.FirstFloor, 
					h.MainFloor, 
					h.ThirdFloor, 
					h.TotalThreeFloors, 
					f.FourthFloor, 
					(h.TotalThreeFloors + f.FourthFloor) AS TotalThreeFloorsPlusFourthFloor
					FROM ThreeFloors h
					right JOIN FourthFloor f 
						ON h.Date=f.DateFourthFloor 
						and h.Hour=f.HourFourthFloor
		) j
	)s
	order by isnull(ID,FourthFloorID)

 

[rootl]

Thank you! This is perfect!