CODEWITHSUNDEEP
postgresql
Sravan Reddy
Sravan Reddy

Reputation: 51

How can we achieve the below output using two source tables (PostgreSQL)

Can you please help me in getting the below output using 2 different tables?

Table 1: has below data enter image description here

Table 2: has below data

enter image description here

The output table should be with below data:

enter image description here

Please help me to achieve this output, Sorry if I missed anything

Thanks in Advance!!

Upvotes: 0

Views: 33

Answers (1)

Tom Mac
Tom Mac

Reputation: 9853

You could split the sub-select into 3 separate queries and then use UNION to both "glue" the results set together and de-duplicate the results set where the name1 columns match between the two table e.g. "John".

Something like this :

insert into target(id,name1,name2,name3)
select src1.id,src1.name1,src2.name1,src1.name1
from src1
inner join src2 on src1.id = src2.id
where src1.name1 is not null
and src2.name1 is not null
union
select src1.id,src1.name1,src2.name1,src2.name1
from src1
inner join src2 on src1.id = src2.id
where src1.name1 is not null
and src2.name1 is not null
union
select src1.id,src1.name1,src2.name1,coalesce(src1.name1,src2.name1)
from table1 src1
inner join table2 src2 on src1.id = src2.id
where src1.name1 is null
or src2.name1 is null
order by 1

Handling the null values in a separate select since UNION will not de-duplicate null values

Upvotes: 1

Related Questions