Trying to create multiple temporary tables in a single query

Question

I'd like to create 3-4 separate temporary tables within a single BigQuery query (all of the tables are based on different data sources) and then join them in various ways later on in the query.

I'm trying to do this by using multiple WITH statements, but it appears that you're only able to use one WITH statement in a query if you're not nesting them. Each time I've tried, I get an error saying that a 'SELECT' statement is expected.

Am I missing something? I'd prefer to do this all in one query if at all possible.

Answer 1

I don't know what you mean by "temporary tables", but I suspect you mean common table expressions (CTEs).

Of course you can have a query with multiple CTEs. You just need the right syntax:

with t1 as (
      select . . .
     ),
     t2 as (
      select . . .
     ),
     t3 as (
      select . . .
     )
select *
from t1 cross join t2 cross join t3;