BigQuery - Joining on multiple conditions using subqueries and OR statements

google-bigquery, join, sql

Solution

You can write separate queries, then use `COALESCE`:

SELECT
  *
FROM
  (
    SELECT
      offer_table.offer_id
      ,COALESCE(c1.customer_name,c2.customer_name,c3.customer_name)
      ,COALESCE(c1.visit_count,c2.visit_count,c3.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 c1
      ON offer_table.customer_id = customer_table.customer_id
    LEFT JOIN customer_table c2
      ON offer_table.email = customer_table.email
    LEFT JOIN customer_table c3
      ON offer_table.phone = customer_table.phone
   )
 ) AS dummy
WHERE
  customer_visit_rank = 1

Problem

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?

Original source