sql - BigQuery - Joining on multiple conditions using subqueries and OR statements

Question

Is there anyway to join two tables on multiple potential conditions?

I'm currently migrating some code from Postgres to Bigquery where I joined on multiple potential values like:

SELECT
 *
FROM
 (
 SELECT
   offer_table.offer_id
   ,customer_table.customer_name
   ,customer_table.visit_count
   ,ROW_NUMBER() OVER (PARTITION BY offer_table.offer_id ORDER BY customer_table.visit_count DESC) AS customer_visit_rank
 FROM
   offer_table
   LEFT JOIN customer_table ON
    (
    offer_table.customer_id = customer_table.customer_id
    OR offer_table.email = customer_table.email
    OR offer_table.phone = customer_table.phone
    )
 ) dummy
WHERE
  customer_visit_rank = 1

I needed to this because my offer and customer data had inconsistent usage of our id, email, and phone fields but all were valid potential matches. If multiple fields worked (ex: id and email matched), there would be duplicate rows and I'd filter them out based on the row_number column after ranking using the ORDER BY section.

However when I try to join on multiple conditions in BigQuery, I get this error message:

LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.

Has anyone figured out a solution to join on multiple values instead of doing the above?

Answer 1

Waitting for answers