title: how to do subqueries in bigquery?
body: <p>Im trying to play with the reddit data on bigquery and I want to see comments and replies in one row.  I see bigquery supports subqueries, but I am unable to construct the query.  I have to use a subquery to self join the same table because of the structure of the data, specifically i want to join id and parent_id together, but I need to modify id before I can join.  Here is how im trying to do the query:</p>
  
  <pre><code>SELECT 
    p.subreddit, 
    p.body AS first_body,
    p.score AS first_score,
    CONCAT('t1_',p.id) AS first_id ,
    c.last_body,
    c.last_score,
    c.last_id 
  FROM 
  [fh-bigquery:reddit_comments.2016_01] p,
  (
    SELECT 
      body AS last_body,
      score AS last_score,
      CONCAT('t1_',id) AS last_id,
      parent_id,
      author,
      body 
    FROM  [fh-bigquery:reddit_comments.2016_01] 
    WHERE body != '[deleted]' 
    AND author != '[deleted]' 
    AND score &gt; 1
  )  c
  WHERE  p.first_id = c.parent_id  
  AND p.score &gt; 1 
  AND  p.author != '[deleted]' 
  AND p.body != '[deleted]';
  </code></pre>
  
  <p>The error I get is:</p>
  
  <pre><code>Field 'c.parent_id' not found in table 'fh-bigquery:reddit_comments.2016_01'; did you mean 'parent_id'?
  </code></pre>
  
  <p>Here is where you can run the query:
  <a href="https://bigquery.cloud.google.com/table/fh-bigquery:reddit_comments.2016_01" rel="nofollow">https://bigquery.cloud.google.com/table/fh-bigquery:reddit_comments.2016_01</a></p>
  
  <p>Im not sure how to fix this.  what is the proper way to join this and get this query to run?</p>