Two database data contrast, master for help

The two annual database, there are project data, I want to make a statement two year data of each project and increases contrast. The two year of the project are to be listed contrast, if only one year only some items to list, the annual data without this project is displayed as 0 details are as follows

Table 1 Table 2
Name of project data item name data
A 10 A 20
B 20 C 30
C 30 E 50
D 40


Results: name of project data 1 data 2
A 10 20
B 20 0
C 30 30
D 10 0
E 0 50

Started by Benedict at November 13, 2016 - 7:19 PM

--> Test data: [table 1]
if object_id('[table 1]') is not null drop table [table 1]
go 
Create table 1] (varchar [table] [name of the project (1), [data] int)
Insert [table 1]
select 'A',10 union all
select 'B',20 union all
select 'C',30 union all
select 'D',40
- start query --------------------------


--> Test data: [table 2]
if object_id('[table 2]') is not null drop table [table 2]
go 
Create table 2] (varchar [table] [name of the project (1), [data] int)
Insert [table 2]
select 'A',20 union all
select 'C',30 union all
select 'E',50


Select ISNULL (a.[project name], the b.[project name]) AS [project name], ISNULL (A., 0) [ISNULL (1] data, B. data, 0) [2] data
From 1] a full JOIN [[table table 2] B ON a.[the name of the project]=b.[project name]
The results ---------------------------- situation
/* 
Name of project data 1 data 2
---- ----------- -----------
A    10          20
B    20          0
C    30          30
D    40          0
E    0           50
*/

Posted by May at November 22, 2016 - 7:30 PM

if object_id('[table 1]') is not null drop table [table 1]
go
Create table [1] (project name nvarchar (2), int data)
Insert into [table 1]
select 'A',10 union all
select 'B',20 union all
select 'C',30 union all
select 'D',40

if object_id('[table 2]') is not null drop table [table 2]
go
Create table [2] (project name nvarchar (2), int data)
Insert into [table 2]
select 'A',20 union all
select 'C',30 union all
select 'E',50

Select * from [table 1]
Select * from [table 2]


Select s. The name of the project, isnull (A., 0) isnull (1 as data, B. data, as data, 2 from 0) (
The select project name
From [table 1]
union 
The select project name
From [table 2]) S
Left join 1] A on S. [project name =A. project name
Left join 2] B on S. [project name =B. project name

/*
A	10	20
B	20	0
C	30	30
D	40	0
E	0	50*/

Posted by Timothy at December 04, 2016 - 7:57 PM

Fully connected?

Posted by Kenny at December 08, 2016 - 8:37 PM

So you can:



if object_id('tb1') is not null drop table tb1
go

Create table tb1 (project name nvarchar (2), int data)
insert into tb1
select 'A',10 union all
select 'B',20 union all
select 'C',30 union all
select 'D',40

if object_id('tb2') is not null drop table tb2
go
Create table TB2 (project name nvarchar (2), int data)
insert into tb2
select 'A',20 union all
select 'C',30 union all
select 'E',50


;with t
as
(
The select project name
from tb1
union 
The select project name
from tb2
)

Select T. the name of the project,
       Coalesce (t1., 0) as data 1 ,
       Coalesce (t2., 0) as data 2 
from t
Left join tb1 T1 on t. =t1. the name of the project project name
Left join TB2 T2 on t. =t2. the name of the project project name
/*
Name of project data 1 data 2
A	10	20
B	20	0
C	30	30
D	40	0
E	0	50
*/

Posted by Tamara at December 17, 2016 - 9:01 PM