CODEWITHSUNDEEP
sql
SUKH
SUKH

Reputation: 33

How to combine two different tables with different columns

I have a situation where I need to combine two different tables with different columns.

As shown in this screenshot

DDL:

create table tableA ([timestamp] datetime, [source] char(1), [description] varchar(20));
insert into tableA values
('2018-10-12', 'a', 'first day'),
('2018-10-13', 'b', 'alarms'),
('2018-10-14', 'c', 'processing');

create table tableB ([timestamp] datetime, entity varchar(20));
insert into tableB values
('2018-10-12', 'new env'),
('2018-10-13', 'resource'),
('2018-10-18', 'integrated');

I have different columns in two different tables. And I need to combine it as shown in screenshot using SQL.

Upvotes: 2

Views: 215

Answers (4)

user2042214
user2042214

Reputation: 165

Use Below code

SELECT isnull(t1.TimeStamp, t2.TimeStamp) TimeStamp, t1.source,t1.description, t2.entity from table1 t1 FULL OUTER JOIN table2 t2 on t1.id=t2.id

Upvotes: 0

Ashton
Ashton

Reputation: 174

You can use INNER JOIN for this

SELECT a.TimeStamp, a.Source, a.Description, b.Entity 
FROM TableA a
LEFT JOIN Tableb b ON a.TimeStamp=b.TimeStamp; 
UNION
SELECT a.TimeStamp, a.Source, a.Description, b.Entity 
FROM TableA a
RIGHT JOIN Tableb b ON a.TimeStamp=b.TimeStamp;

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37337

You need to use full join. Try this query:

select coalesce (a.timestamp, b.timestamp), source, description, entity
from tableA a
full join tableB b on a.timestamp = b.timestamp

Demo

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

Use union all

select a.timestamp, a.source,a.description,b.entity
from tableA a left join tableB b on a.timestamp=b.timestamp 
where b.timestamp is not null
union all
select b.timestamp, a.source,a.description,b.entity
from tableA a right join tableB b on a.timestamp=b.timestamp 
where a.timestamp is null

Upvotes: 2

Related Questions