CODEWITHSUNDEEP
google-bigquery
nrad
nrad

Reputation: 93

Joining condition for the same column

I have two tables as shown in the image. I would like to join two table. As shown in thane tables, first row is matching exactly, but in the second row last 17 digits(WSAV_3PE_RET_0720) are matching. I would like join this one also. So there should be two conditions.

  1. Exactly matching
  2. Last 17 digits are matching.

How to write the code in this scenario. Here is my partial code.

Select *,t2.Balance from t1 left join t2 on t1.Place = t2.Place

enter image description here

Upvotes: 0

Views: 27

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172964

Below is for BigQuery Standard SQL

#standardSQL
SELECT t1.*, t2.Balance
FROM `project.dataset.table1` t1
LEFT JOIN `project.dataset.table2` t2
ON SUBSTR(REVERSE(t1.Place), 1, 17) = SUBSTR(REVERSE(t2.Place), 1, 17)

Upvotes: 1

Related Questions