Postgres- merge 2 different query results

28 Views Asked by At

I have 3 tables table1,table2,maintable:

table1

tb1col1 tb1col2 maintable_id
abc John 123
dof Mat 123
cda Mary 123
xyz Max 687

table2

tb2col1 tb2col2 maintable_id
A 2.0 123
B 5.0 123
C 11.0 345
A 2.0 687

maintable

id date
123 2023-09-10
345 2023-09-11

I want to merge 2 queries with below result :

tb1col1 tb1col2 tb2col1 tb2col2
abc John A 2.0
dof Mat B 5.0
cda Mary

Ive tried select * from (select tb1col1,tb1col2 from maintable m left join table1 tb1 on tb1.maintable_id=m.id where date='2023-09-10')y full join (select tb2col1,tb2col2 from maintable m left join table2 tb2 on tb2.maintable_id=m.id where date='2023-09-10')x on y.maintable_id=x.maintable_id;

which gives me:

tb1col1 tb1col2 tb2col1 tb2col2
abc John A 2.0
abc John B 5.0
dof Mat A 2.0
dof Mat B 5.0
cda Mary A 2.0
cda Mary B 5.0

Ive also tried UNION

(select tb1col1,tb1col2,NULL as tb2col1,NULL as tb2col2 from maintable m left join table1 tb1 on (tb1.maintable_id=m.id) union all (select NULL as tb1col1,NULL as tb1col2,tb2col1,tb2col2 from maintable m left join table2 tb2 on tb2.maintable_id=m.id);

which gives me :

tb1col1 tb1col2 tb2col1 tb2col2
abc John
dof Mat
cda Mary
A 2.0
B 5.0

But expected result is

tb1col1 tb1col2 tb2col1 tb2col2
abc John A 2.0
dof Mat B 5.0
cda Mary
0

There are 0 best solutions below