Joining 2 tables in pyspark, multiple conditions, left join?

Question

I have 2 tables like below. My code below is joining 2 tables (left join). The problem is I have to do the same join twice. The first join is happening on log_no and LogNumber which returns all records from the left table (table1), and the matched records from the right table (table2). The second join is doing the same thing but on the substring of log_no with LogNumber. for example, 777 will match with 777 from table 2, 777-A there is no match but when using a substring function 777-A becomes 777 which will be matched in table 2.

Instead of creating 2 separate joins like below, how can I cover both scenarios with one join instead. Code below:

# first join to match 1234-A (table 1) with 1234-A (table 2)
df5 = df5.join(df_app, trim(df5.LOG_NO) == trim(df_app.LogNumber), "left")\
         .select (df5["*"], df_app["ApplicationId"])
df5 = df5.withColumnRenamed("ApplicationId","ApplicationId_1")
        
# second join with substring function, to match 777-C with 777, 
# my string is longer than my examples, this is why I have a substring for the first 8 characters. I provided simple examples.

df5 = df5.join(df_app, substring(trim(df5.LOG_NO), 1, 8) == trim(df_app.LogNumber), "left")\
         .select (df5["*"], df_app["ApplicationId"])
df5 = df5.withColumnRenamed("ApplicationId","ApplicationId_2")

mck mck · Accepted Answer · 2020-11-06T06:46:51

You can combine the two join conditions using a bitwise OR:

df5 = df5.join(df_app, 
               (trim(df5.LOG_NO) == trim(df_app.LogNumber)) |
               (substring(trim(df5.LOG_NO), 1, 8) == trim(df_app.LogNumber)), 
               "left") \
         .select(df5["*"], df_app["ApplicationId"])

Joining 2 tables in pyspark, multiple conditions, left join?

1 Answers