CODEWITHSUNDEEP
sqlpostgresql
user19031918
user19031918

Reputation:

How to merge two columns in join query?

I have tow tables

table 1

id |   name   |  birthdate
1  | 'sylvie' |  2016-06-01
2  | 'rolf'   |  -
3  | 'jose'   |  2004-02-16
4  | 'Eugin'  |  -
5  | 'andrey' |  1998-09-29
6  | 'ivan'   |  2000-05-12
7  | 'vasiys' |  2001-07-17
8  | 'alexey' |  1993-09-05

Table 2

id |   name   | date_of_birth
1  | 'sylvie' |  -
2  | 'rolf'   |  2015-12-06
3  | 'jose'   |  -
4  | 'Eugin'  |  1995-04-01
5  | 'andrey' |  1998-09-29

if i run this query :

select t1.id, t1.name, t1.birthdate, t2.date_of_birth from table1 t1
left join table2 t2
on t1.id = t2.id --(or using(id))

the result is

id |   name   |  birthdate   | date_of_birth
1  | 'sylvie' |  2016-06-01  | -
2  | 'rolf'   |  -           | 2015-12-06
3  | 'jose'   |  2004-02-16  | - 
4  | 'Eugin'  |  -           | 1995-04-01
5  | 'andrey' |  1998-09-29  | 1998-09-29
6  | 'ivan'   |  2000-05-12  | -
7  | 'vasiys' |  2001-07-17  | -
8  | 'alexey' |  1993-09-05  | -

but I need a table that combine the both columns birthdate from table1 and date_of_birth from table1. the result should be like that:

id |   name   |  birth_date
1  | 'sylvie' |  2016-06-01  
2  | 'rolf'   |  2015-12-06 
3  | 'jose'   |  2004-02-16
4  | 'Eugin'  |  1995-04-01
5  | 'andrey' |  1998-09-29 
6  | 'ivan'   |  2000-05-12
7  | 'vasiys' |  2001-07-17 
8  | 'alexey' |  1993-09-05

Upvotes: 2

Views: 81

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

You want a full outer join here:

select coalesce(t1.id, t2.id) as id,
       coalesce(t1.name, t2.name) as name,
       coalesce(t1.birthdate, t2.date_of_birth) as birthdate
from table1 t1
full outer join table2 t2
    on t1.id = t2.id
order by coalesce(t1.id, t2.id)

Upvotes: 2

Related Questions