CODEWITHSUNDEEP
google-bigquery
Marcel
Marcel

Reputation: 284

BigQuery query without join gives join error on "not in" usage

When I do select from a sub-select and finally want to do a "not in" with the results, BQ query gives the following error:

Error: Join attribute is not defined: t1.customer_id

When I change the "not in" part with a simple where t1.customer_id = 1 for a example, the query executes.

The query does not look like a join but from the error, BigQuery seems to think it is. 

select t1.customer_id , GROUP_CONCAT(t1.id)  from  ( 
   select  customer.id as customer_id, id
  from (TABLE_QUERY(redacted , 'table_id in     ( "x_201502",     "x_201503")'))
  where created >= '2014-09-05 00:00:00' 
  and created < '2015-03-04 00:00:00' 
  group by customer_id, id
) t1
where  t1.customer_id not in (
  select customer.id as customer_id 
  from (TABLE_QUERY(redacted , 'table_id in ("y_201503")'))
  where created >= '2015-03-03 18:55:59' 
  group by customer_id
) 
group by t1.customer_id;

I initially tried to put the "not in" sub select in the subselect but BQ threw errors for that approach as well, hence I am attempting this construct. Note that the "not in" part does work when following a join of 2 subselects.

Any ideas on how to accomplish the 'not in' check in this particular case?

Upvotes: 4

Views: 859

Answers (1)

Mosha Pasumansky
Mosha Pasumansky

Reputation: 13994

This is SQL incompatibility in BigQuery. As a workaround, I think using just WHERE customer_id NOT IN (...) should work.

Upvotes: 5

Related Questions