[Solved] I need to join 2 databases, with 2 tables each

by


With a UNION ALL you can get 1 combined resultset from 2 selects.
Then you can group that and SUM the amounts per date.

So you’re probably looking for something like this: 

select 
 q.ID,
 q.Name,
 nullif(sum(case when q.Date="2018-05-01" then q.Amount end), 0) as "5/1/2018",
 nullif(sum(case when q.Date="2018-05-02" then q.Amount end), 0) as "5/2/2018"
from
(
  select u1.ID, u1.Name, a1.Date, a1.Amount
  from DB1.Table1 AS u1
  join DB1.Table2 AS a1 on (a1.ID = u1.ID and a1.Amount is not null)
  where a1.Date IN ('2018-05-01', '2018-05-02')

  union all -- combines the results of the 2 selects into one resultset

  select u2.ID, u2.Name, a2.Date, a2.Amount
  from DB2.Table1 AS u2
  join DB2.Table2 AS a2 on (a2.ID = u2.ID and a2.Amount is not null)
  where a2.Date IN ('2018-05-01', '2018-05-02')
) AS q
group by q.ID, q.Name
order by q.ID;

An alternative is to JOIN them all up. 

select 
coalesce(a1.ID, a2.ID) as ID,
max(coalesce(u1.Name, u2.Name)) as Name, 
max(case 
    when coalesce(a1.Date, a2.Date) = '2018-05-01'
     and coalesce(a1.Amount, a2.Amount) is not null
    then coalesce(a1.Amount, 0) + coalesce(a2.Amount, 0) 
    end) as "5/1/2018",
max(case 
    when coalesce(a1.Date, a2.Date) = '2018-05-02'
     and coalesce(a1.Amount, a2.Amount) is not null
    then coalesce(a1.Amount, 0) + coalesce(a2.Amount, 0) 
    end) as "5/2/2018"
from DB1.Table2 AS a1 
full join DB2.Table2 AS a2 on (a2.ID = a1.ID and a2.Date = a1.Date)
left join DB1.Table1 AS u1 on (u1.ID = a1.ID)
left join DB2.Table1 AS u2 on (u2.ID = a2.ID)
where coalesce(a1.Date, a2.Date) IN ('2018-05-01', '2018-05-02')
group by coalesce(a1.ID, a2.ID)
order by coalesce(a1.ID, a2.ID);

But then note that this way, that there’s an assumption that the two Table2 have a uniqueness on (ID, Date)

T-Sql test data:
declare @DB1_Table1 table (id int, Name varchar(30));
declare @DB2_Table1 table (id int, Name varchar(30));
declare @DB1_Table2 table (id int, [Date] date, Amount decimal(8,2));
declare @DB2_Table2 table (id int, [Date] date, Amount decimal(8,2));
insert into @DB1_Table1 (id, Name) values (1,’Susan’),(2,’Juan’),(3,’Tracy’),(4,’Jenny’),(5,’Bill’);
insert into @DB2_Table1 (id, Name) values (1,’Susan’),(2,’Juan’),(3,’Tracy’),(4,’Jenny’),(5,’Bill’);
insert into @DB1_Table2 (id, [Date], Amount) values
(1,’2018-05-01′,20),(2,’2018-05-01′,null),(3,’2018-05-01′,30),(4,’2018-05-01′,50),(5,’2018-05-01′,null),
(1,’2018-05-02′,15),(2,’2018-05-02′,40),(3,’2018-05-02′,25),(4,’2018-05-02′,8),(5,’2018-05-02′,null);
insert into @DB2_Table2 (id, [Date], Amount) values
(1,’2018-05-01′,null),(2,’2018-05-01′,15),(3,’2018-05-01′,20),(4,’2018-05-01′,10),(5,’2018-05-01′,null),
(1,’2018-05-02′,15),(2,’2018-05-02′,30),(3,’2018-05-02′,35),(4,’2018-05-02′,null),(5,’2018-05-02′,30);

21

solved I need to join 2 databases, with 2 tables each