0

I have two table that I want to combine without FULL OUTER JOIN as it not work in h2 db. I have to create select sql query:I have tried much with UNION too but I think it will not be possible with UNION.

Table 1

id     c_id       s_id           p_date
---------------------------------------------
1       1          1              2020-10-10
2       1          1              2020-10-11
3       2          1              2020-10-11
4       2          2              2020-10-12

Table 2

id     c_id       s_id           s_date
---------------------------------------------
1       1          1              2020-10-15
2       1          2              2020-10-16
3       2          2              2020-10-17
4       2          2              2020-10-17

I am expecting below result:

    c_id       s_id      p_date        s_date
    -------------------------------------------------
    1          1        2020-10-10      2020-10-15
    1          1        2020-10-11         -
    1          2            -           2020-10-16
    2          1        2020-10-11         -
    2          2        2020-10-12      2020-10-17
    2          2            -           2020-10-17

Please help to get this result.

rahul
  • 41
  • 1
  • 5

1 Answers1

1

You can use union all like this:

select t1.c_id, t1.s_id, t1.p_date, t2.s_date
from table1 t1 left join
     table2 t2
     on t1.c_id = t2.c_id and t1.s_id = t2.s_id
union all
select t2.c_id, t2.s_id, t1.p_date, t2.s_date
from table2 t2 left join
     table1 t1
     on t1.c_id = t2.c_id and t1.s_id = t2.s_id
where t1.c_id is null;

The first subquery gets all the rows where there are matches between the two tables plus rows where table2 has no match for table1.

The second subquery gets the additional rows from table2 that have no match in table1.

Here is a db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • No. It is not generating desired result not even close the result. Only 3 row is getting with repeated s_date. – rahul Aug 10 '20 at 22:08
  • @rahul . . . .Then you are doing something wrong, because it returns the correct results when I run the code. I've included a db<>fiddle. – Gordon Linoff Aug 10 '20 at 22:38
  • I ran exactly same query as you posted it. but my expected result is little different.. I have tried it all. – rahul Aug 11 '20 at 08:29
  • @Rahul . . . Look at the db<>fiddle. The query is running there. – Gordon Linoff Aug 11 '20 at 10:56