Multiple Joins

[JKWA]

I attempted to join two small (5.5 KB, 18.2 KB) tables with a larger one and received this error

Query Failed

Error: multiple JOIN clauses are not allowed at: 11.1 - 12.65

Both joins work fine by themselves.  Is there something that I'm missing for multiples?

Joe

[Jordan Tigani]

Can you try using a nested select statement? In other words, do the first join inside an inner select, then the second inside an outer one?

For example, I have too small tables foo.seventeen and foo.eighteen, each of which have the single field f0_

I can join these both with the publicdata:samples.wikipedia table with the following query:

select baz.id, baz.eieio 

    from (

        select wiki.id as id, wiki.revision_id as eieio 

            from publicdata:samples.wikipedia as wiki 

            join bar.eighteen as ei on wiki.id = ei.f0_) as baz 

    join bar.seventeen as se on baz.eieio = se.f0_;

Can you give this a try with your tables?

thanks

-jordan

[Joseph Koski]

Thanks for your suggestions Jordan.  I tried nesting select statements but didn't have any luck.  

As I read the documentation, joining two small tables should be as simple as just writing 2 JOIN statements.  But in my experience their sample query:

SELECT purchases.amount, products.name, users.email_address
FROM   PurchaseData AS purchases
JOIN   UserData AS users
       ON purchases.user_id = users.user_id
JOIN   ProductData AS products
       ON purchases.product_id = products.product_id

Would return 

Error: multiple JOIN clauses are not allowed at: 5.1 - 6....

[Craig Citro]

Hi Joseph,

BigQuery supports multiple joins, but a single SELECT statement can only contain one JOIN clause, one (or both) of whose clauses can themselves be SELECT statements. (It's also worth noting that while we don't support 'SELECT * FROM ...' at top-level, we do support it in subselects.) This means that you have to manually insert an additional SELECT for each additional JOIN. (Doing this automatically is on our todo list, but hasn't happened yet.) It looks like that example from our docs needs to get cleaned up; there are several ways to skin this cat, but I think the most natural would be:

SELECT joined.purchases.amount, products.name, joined.users.email_address
FROM
 (SELECT *

  FROM PurchaseData AS purchases
  JOIN UserData AS users

  ON purchases.user_id = users.user_id) AS joined
JOIN ProductData AS products
ON joined.purchases.product_id = products.product_id

You'll notice that I've basically just rewritten the first JOIN into a SELECT, and added one extra layer for the names of the joined tables. Alternately, you could explicitly select the fields you needed:

SELECT purchases.amount, products.name, purchases.email_address
FROM
 (SELECT purchases.amount AS amount,
         users.email_address AS email_address,

         purchases.product_id AS product_id

  FROM PurchaseData AS purchases
  JOIN UserData AS users

  ON purchases.user_id = users.user_id) AS purchases

JOIN ProductData AS products
ON purchases.product_id = products.product_id

Does that make sense? Feel free to reply back if you're curious about how to do this in your case.

Thanks for pointing out this example in the docs -- we'll get it fixed soon!

--

-cc

[JKWA]

Got it.  I was confused about this particular concept  'joined.purchases.amount ' 
Now it works just as expected.

Thanks for the help Craig and Jordan,

Joe