score:3

Accepted answer

the problem of the sql is that, the result has two columns (key) with the same name from the two join tables.

solution #1 assign different names to the keys.
e.g. set the column name of the left table to be k1
set the column name of the right table to be k2

solution #2 specify the columns you want to keep in the result table

select a.*, b.val1, b.val2
from tab_a a left outer join tab_b b on a.key = b.key and a.e_date between b.start_date and b.end_date 


// since you you only want to keep one key, please change the code you have
val result_df = tab_a.join(tab_b,tab_a.col("key") === tab_b.col("key")
                         && tab_a.col("e_date").between(tab_b.col("start_date"),tab_b.col("start_date")),
                    "left_outer")
// drop the key from tab_b or tab_a
val result_df = tab_a.join(tab_b,tab_a.col("key") === tab_b.col("key")
                         && tab_a.col("e_date").between(tab_b.col("start_date"),tab_b.col("start_date")),
                    "left_outer").drop(tab_b("key"))

Related Query

More Query from same tag