how to do subqueries in bigquery?
<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>