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 |