Google BigQuery: join on an join Alias

i try to perform an join on a table that is the result of an other join. But i recieve the error: Table “join_1” must be qualified with a dataset (e.g. dataset.table). I bolded the Problem. Thanks a lot in advance.

How can I qualify table join_1? I edited the table, I hope that helps to understand the problem more. Thanks a lot, you guys reply that fast, you guys are awesome.

SELECT
  *
FROM (

SELECT
  *
FROM
  `prod.box.records` AS records


LEFT JOIN 

  `prod.box.record_updates` as updates

USING
  (c2)

WHERE c11 ="view") AS join_1

INNER JOIN

(
  SELECT
    c1,
    c6,
    c2
  FROM (
    SELECT
      c1,
      IFNULL(c4,c3) AS c6,
      c2,
      c7
    FROM (
      SELECT
        c1,
        c2,
        c3,
        c4,
        c5
      FROM
        **join_1**)

    LEFT JOIN
(
      SELECT
        c1,
        c2,
        MAX(c5) AS c5,
        "TRUE" AS c7
      FROM
        join_1
      GROUP BY
        c5,
        c2)
    USING
      (c2, c1, c5)
    GROUP BY
      c1,
        c6,
        c2,
      c7
    HAVING
      c7 = "TRUE")) AS status


USING
  (c2,
    c1)

…

Answer

I found the answer to my Problem, but I was only able to find it, because of your “with clause” hint, samuel. So, thanks a lot, Samuel. But I will post the answer here, I have put all Joins into With clauses just the last one i did in the SELECT.

with 
join_1 as (
SELECT *
FROM
prod.box.records AS records
LEFT JOIN
prod.box.record_updates AS updates
USING
(c2)
WHERE c11 ="view"), status as (
SELECT
    c1,
    c6,
    c2
  FROM (
    SELECT
      c1,
      IFNULL(c4,c3) AS c6,
      c2,
      final_status
    FROM (
      SELECT
        c1,
        c2,
        c3,
        c4,
        c5
      FROM
        join_1)
    LEFT JOIN (
      SELECT
        c1,
        c2,
        c5,
        "TRUE" AS final_status
      FROM
        join_1
      GROUP BY
        c5,
        c2)
    USING
      (c2, c1, c5)
    GROUP BY
      c1,
        c6,
        c2,
      final_status
    HAVING
      final_status = "TRUE")

USING
  (c2,
    c1))
SELECT
  *
FROM join_1
INNER JOIN
status
USING
  (c2,
    c1)