BigQuery JOIN Error

5
votes

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.

2
sqlgoogle-bigquery
The AND part of your ON clause doesn't specify a join condition. Change it to a WHERE. - antlersoft

2 Answers

2
votes

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.

0
votes

Presumably, adding in the aliases would solve the problem. I also don't think the subquery is necessary:

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

The each may not be necessary, but I am not overly familiar with Google BigQuery.