Using Aliases in the ON Clause of a JOIN

Question

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!

Please indicate which database system you are working on and remove unnecessary tag. You have tagged both BigQuery and MySql. Thank you. — YABADABADOU, Dec 18 '14 at 15:05
Oops! I did not realize that MySQL was a database! I thought that was the language we were writing in. I am working on a BigQuery database. Thanks! — Ben Leathers, Dec 18 '14 at 16:32

score 3 · Answer 1 · answered Dec 18 '14 at 19:35

3

Try using two subqueries like this:

SELECT
a.firstname
,a.lastname
,a.email1
,a.total_donated
FROM

(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

In your original query, a is just an alias for BensData.Donations, so you can only join on fields present in that table.

answered Dec 18 '14 at 19:35

sprocket

1,217
10
10

Hey Sprocket, I'm still having trouble joining ON aliases even when I join on two sub queries as you suggested. I tried a simpler version of your solution: `SELECT a.firstname FROM (SELECT lower(first_name) as firstname From BensData.Donations) As a JOIN EACH (Select lower(first_name) as first From BensData.Donations) As b ON a.firstname=b.first` And it's still giving me the following error: "The Google BigQuery service was unable to compile the query. Field 'a_firstname' not found; did you mean 'a.firstname'?" Which I find strange, because "a_firstname" isn't even in the code Thanks – Ben Leathers Dec 20 '14 at 01:01
In an attempt to be as clear as possible, I've been unable to JOIN on aliases I've created for dimensions. The table aliases work fine. Thanks! – Ben Leathers Dec 20 '14 at 01:32

score 0 · Answer 2 · answered Dec 18 '14 at 02:53

0

I have never heard of join each and it is not documented as a syntax for MySQL joins (see here).

Try this from clause:

From BensData.Donations a JOIN
     (Select lower(first_name) as first, lower(last_name) as last,
             sum(amount) as total_donated
      From BensData.Donations 
      GROUP BY first, last
     ) b
     ON a.firstname = b.first AND a.lastname = b.last

Your version was also missing commas in the subquery between the columns.

For clarity, you should probably write the from clause as:

     ON lower(a.firstname) = b.first AND lower(a.lastname) = b.last

or remove the lower() from the subquery. Otherwise, you are dependent on the default collations for the server, database, and table for what the on clause really does.

answered Dec 18 '14 at 02:53

Gordon Linoff

1,242,037
58
646
786

Here's the documentation that led me to using 'JOIN EACH' https://cloud.google.com/bigquery/query-reference It wouldn't work without the word 'EACH' for some reason In my original post I re-wrote my code to try and get to the core of my question, but in doing so, it seems I forgot some commas. – Ben Leathers Dec 18 '14 at 03:14
Here's the code I originally had, which works, but does not address the case correctly: `SELECT first_name ,last_name ,email ,amount ,total_donated FROM SweetData.Payments 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 SweetData.Payments GROUP BY email2, LAST, FIRST) AS b ON a.email=b.email2 AND a.last_name=b.LAST AND a.first_name=b.FIRST` – Ben Leathers Dec 18 '14 at 03:16
Unfortunately, BigQuery does not seem to like wrapping the dimensions in the ON CLAUSE in the lower() function. When I swap out the ON clause with the following I get an error: `ON lower(a.email)=b.email2 AND lower(a.last_name)=b.LAST AND lower(a.first_name)=b.FIRST` Thanks – Ben Leathers Dec 18 '14 at 03:19
@BenLeathers . . . Hmmm, MySQL doesn't have a limitation like that. – Gordon Linoff Dec 18 '14 at 12:31
Hey Gordon, Thanks for your reply, I thought MySQL was the language we were writing in, not a database system. I am working on a BigQuery database. – Ben Leathers Dec 18 '14 at 16:36

score 0 · Accepted Answer · answered Jan 06 '15 at 03:38

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!

Using Aliases in the ON Clause of a JOIN

3 Answers3