title: SQL: how to select a single id ("row") that meets multiple criteria from a single column
body: <p>I have a very narrow table: user_id, ancestry.</p>
  
  <p>The user_id column is self explanatory.</p>
  
  <p>The ancestry column contains the country from where the user's ancestors hail.</p>
  
  <p>A user can have multiple rows on the table, as a user can have ancestors from multiple countries.</p>
  
  <p>My question is this: how do I select users whose ancestors hail from multiple, specified countries?</p>
  
  <p>For instance, show me all users who have ancestors from England, France and Germany, and return 1 row per user that met that criteria.</p>
  
  <p>What is that SQL?</p>
  
  <pre><code> user_id     ancestry
  
  ---------   ----------
  
      1        England
      1        Ireland
      2        France
      3        Germany
      3        Poland
      4        England
      4        France
      4        Germany
      5        France
      5        Germany
  </code></pre>
  
  <p>In the case of the data above, I would expect the result to be "4" as user_id 4 has ancestors from England, France and Germany.</p>
  
  <p>Thanks in advance.</p>
  
  <p>P.S. To clarify: Yes, the user_id / ancestry columns make a unique pair, so a country would not be repeated for a given user.</p>
  
  <p>P.P.S. I am looking for users who hail from all 3 countries - England, France, AND Germany (and the countries are arbitrary).</p>
  
  <p>P.P.P.S. I am not looking for answers specific to a certain RDBMS.  I'm looking to answer this problem "in general."</p>
  
  <p>I'm content w regenerating the where clause for each query provided generating the where clause can be done programmatically (e.g. that I can build a function to build the WHERE / FROM - WHERE clause).</p>