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.
x
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)
…
Advertisement
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)