[FIXED] How to do joins on temporal tables created using WITH AS in BigQuery?

Issue

I am working in BigQuery and I am using the WITH AS sentence to create temporal tables and then do joins between them, let's name them table_1 and table_2. These tables are complex to create, I mean, they require a lot of work and code, and I need to use them repeatedly and I want to avoid doing big and expensive sub-queries every time, for example, to do left join between them. The thing is when I create table_1 everything is okay, but when I create table_2 and I try to do the left join, a warning pops and tells me that table_1 does not exist, so how can I do this? My code is the following:

WITH table_1 AS ( SELECT key,f2,f3 FROM xxxxxxx WHERE XXXXXXXXX)
SELECT * table_1;

WITH table_2 AS ( SELECT key,f4,f5 FROM xxxxxxx WHERE XXXXXXXXX)
SELECT * table_2;

I want to do a left join between table_1 and table_2 on key, but I can't figure out how to do it. Any hint? thanks in advance and greetings

Solution

You can define multiple expressions with WITH in the same statement to do your join.

WITH table_1 AS ( SELECT key,f2,f3 FROM xxxxxxx WHERE XXXXXXXXX),
table_2 AS ( SELECT key,f4,f5 FROM xxxxxxx WHERE XXXXXXXXX)
SELECT * table_1 LEFT JOIN table_2 ON table_1.key = table_2.key;

Answered By - Sharon Choong

Answer Checked By - Cary Denson (PHPFixing Admin)