title: postgres select where id in array selected from a different table
body: <p>Question from a postgres noob. I have two tables in postgres. See below for schematic.</p>
  
  <pre><code>strains_snps::table
      name::str
      variants_id::int array
  
  variants::table
      id::int
      ref_base::str
  </code></pre>
  
  <p>I want to </p>
  
  <pre><code>select variants_id 
  from strains_snps 
  where strains_snps.name = 'foo' 
  </code></pre>
  
  <p>and then use that variants_id (which is an int array) in a follow up query.</p>
  
  <pre><code>select * 
  from variants 
  where id in the_output_from_previous_query
  </code></pre>
  
  <p>Coming from python, I would assign the output of the first query to a variable, and then check for membership of that variable in the second query. But that probably isn't the best way here, and I hope there is some way to get this working as a single query?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>@Sumit suggested using a sub-query. I tried this, but without success.</p>
  
  <pre><code>select * from variants 
  where id in 
  (select variants_id from strains_snps where name = 'BMD1964')
  </code></pre>
  
  <p>The error pgadmin returned was </p>
  
  <pre><code>ERROR:  operator does not exist: integer = integer[]
  LINE 2: where id in 
               ^
  HINT:  No operator matches the given name and argument type(s). You  
  might need to add explicit type casts.
  
  ********** Error **********
  
  ERROR: operator does not exist: integer = integer[]
  SQL state: 42883
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Character: 34
  </code></pre>