title: Google BigQuery nested select subquery in cross join
body: <p>I have the following code:</p>
  
  <pre><code>SELECT ta.application as koekkoek, ta.ipc, ipc_count/ipc_tot as ipc_share, t3.sfields FROM (
    select t1.appln_id as application, t1.ipc_subclass_symbol as ipc, count(t2.appln_id) as ipc_count, sum(ipc_count) over (PARTITION BY application) as ipc_tot
      FROM temp.tls209_small t1
      CROSS JOIN 
     (SELECT appln_id, FROM temp.tls209_small group by appln_id )  t2 
        where t1.appln_id = t2.appln_id
          GROUP BY application, ipc
        ) as ta
    CROSS JOIN thesis.ifris_ipc_concordance t3
    WHERE ta.ipc LIKE t3.ipc+'%'
      AND ta.ipc NOT LIKE t3.not_ipc+'%'
      AND t3.not_appln_id NOT IN 
         (SELECT ipc_subclass_symbol from temp.tls209_small t5 where t5.appln_id = ta.application)
  </code></pre>
  
  <p>Giving the folllowing error:<br>
  <code>Field 'ta.application' not found.</code></p>
  
  <p>I have tried numerous notations for the field, but BigQuery doesn't seem to recognize any reference to other tables in the subquery.</p>
  
  <p>The purpose of the code is as to assign new technology classifications to records based on a concordance table:</p>
  
  <p>I have got two tables:
  One large table with application id's, classifications and some other stuff <code>tls209_small</code>:<br>
  <img src="https://i.stack.imgur.com/uPsk0.png" alt="tls209_small"></p>
  
  <p>And a concordance table with some exception rules <code>ifris_ipc_concordance</code>:
  <img src="https://i.stack.imgur.com/P2Anl.png" alt="ifris_ipc_concordance"></p>
  
  <p>In the end I need to assign the <code>sfields</code> label for each row in <code>tls209</code> (300 million rows). The rules are that <code>ipc_class_symbol+'%'</code> from the first table should be like <code>ipc</code>in the second table, but not like <code>not_ipc</code>.
  In addition, the <code>not_appln_id</code> value, if present, should not be associated with the same appln_id in the first table.</p>
  
  <p>So a small example, say this is the input of the query:</p>
  
  <pre><code>appln_id | ipc_class_symbol
  1        | A1
  1        | A2
  1        | A3
  1        | C3
  
  sfields | ipc | not_ipc | not_appln_id
  X       | A   | A2      | null
  Y       | A   | null    | A3
  </code></pre>
  
  <p>appln_id 1 should get two times sfields X because ipc=A, not_ipc matches A1 and A3. 
  Y should not be assigned at all as A3 occurs in appln_id 1.</p>
  
  <p>In the results, I also need the share of the <code>ipc_class_symbol</code> for a single application (1 for 328100001, 0.5 for 32100009 etc.)</p>
  
  <p>Without the last condition (<code>AND t3.not_appln_id NOT IN (SELECT ipc_subclass_symbol from temp.tls209_small t5 where t5.appln_id = ta.application)</code> ) the query works fine: 
  <img src="https://i.stack.imgur.com/zPixi.png" alt="results"></p>
  
  <p>Any suggestions on how to get the subquery to recognize the application id (ta.application), or other ways to introduce the last condition to the query?</p>
  
  <p>I realize my explanation of the problem may not be very straightforward, so if anything is not clear please indicate so, I'll try to clarify the issues.</p>