CODEWITHSUNDEEP
sqldatabasepostgresql
Sergey
Sergey

Reputation: 45

table intersection

how to do this? How to join so connect the table?

Tables:

| id | p1 | dfrom | dto |
| 1  | 1  |  2010 |2014 |

| id | p2 | dfrom | dto |
| 1  | 2  | 2013  | 2016|

Result:

| id | p1 | p2 | dfrom | dto |
| 1  | 1  |null| 2010  | 2012|
| 1  | 1  | 2  | 2013  | 2014|
| 1  |null| 2  | 2015  | 2016|

Thanks in advance!

Upvotes: 0

Views: 42

Answers (1)

Naveen Kumar
Naveen Kumar

Reputation: 2006

Try this:

select a.id as id,a.p1 as p1,null as p2,a.dfrom as dfrom,a.dfrom+(a.dto-a.dfrom)/2 as dto 
from test1 a
union
select b.id as id,a.p1 as p1,b.p2 as p2,b.dfrom as dfrom,b.dfrom+(b.dto-b.dfrom)/2 as dto 
from test2 b join test1 a on a.id=b.id
union
select t2.id,null as p1,t2.p2,t1.dto+1 as dfrom,t2.dto from 
(
select b.id as id,a.p1 as p1,b.p2 as p2,b.dfrom as dfrom,b.dfrom+(b.dto-b.dfrom)/2 as dto from test2 b 
join test1 a on a.id=b.id
)t1
join test2 t2 on t1.id=t2.id

Output

1  1    NULL 2010 2012
1  1     2   2013 2014
1  NULL  2   2015 2016

Upvotes: 1

Related Questions