New to Stack Overflow (and coding in general).
I did some research but was unable to find an answer to the following problem:
How can I join two tables ON the results of functions applied to dimensions, rather than on the dimensions themselves?
i.e. I want to join the following two tables on the lowercase results of the function lower() rather than joining on the case ambiguous dimensions as they are.
SELECT
lower(first_name) as firstname
,lower(last_name) as lastname
,lower(email) as email1
,total_donated
From BensData.Donations As a
JOIN EACH
(Select
lower(first_name) as first
,lower(last_name) as last
,lower(email) as email2
,sum(amount) as total_donated
From BensData.Donations
GROUP BY email2, first, last) As b
ON a.email1=b.email2 AND a.firstname=b.first AND a.lastname=b.last
It does not let me join on the aliases I create in the first table (a), however, if I join ON the original dimensions in table a (first_name and last_name) then the results are based on the case ambiguous dimensions, and give an undesired result.
I hope that was clear.
Thanks for any help!
Thanks for everyone's help!
Particularly sprocket who pointed me in the right direction! The main difference in his code and mine is that mine does not have the table aliases appended on the front of each dimension of the first SELECT clause (e.g. **a.**fistname, **a.**lastname, -----> firstname, lastname)
For some reason BigQuery kept giving me an error because of the table aliases.
Here's the code that worked.
SELECT
firstname
,lastname
,email1
,total_donated
FROM
(SELECT
lower(first_name) as firstname
,lower(last_name) as lastname
,lower(email) as email1
From BensData.Donations) As a
JOIN EACH
(Select
lower(first_name) as first
,lower(last_name) as last
,lower(email) as email2
,sum(float(amount)) as total_donated
From BensData.Donations
GROUP BY email2, first, last) As b
ON a.email1=b.email2 AND a.firstname=b.first AND a.lastname=b.last
Thanks all for your help!