BigQuery JOIN Error

Question

I've been trying to solve this error for hours without any luck, its on a query that I've been running for weeks with no issues, but suddenly I am seeing this error:

Error: ON clause must be AND of = comparisons of one field name from each table, with all field names prefixed with table name.

The query is formatted like:

SELECT S.av AS av, S.dm AS dm, t, gn
FROM [dataset.cTable] 
JOIN EACH (SELECT id, av, dm FROM [dataset.sTable]) AS S ON S.id = sid  
AND (t == 'type1' OR t == 'type2')  GROUP EACH BY av, dm, t, gn;

Any help would be greatly appreciated.

Answer 1

The (t == 'type1' OR t == 'type2') clause isn't a join condition, it is a where condition. If you change your query to:

SELECT S.av AS av, S.dm AS dm, C.t, C.gn
FROM [dataset.cTable] C
JOIN EACH (SELECT id, av, dm FROM [dataset.sTable]) AS S ON S.id = sid  
WHERE (C.t == 'type1' OR C.t == 'type2')  GROUP EACH BY S.av, S.dm, C.t, C.gn;

it should work.