SQL: how to select a single id ("row") that meets multiple criteria from a single column

Question

I have a very narrow table: user_id, ancestry.

The user_id column is self explanatory.

The ancestry column contains the country from where the user's ancestors hail.

A user can have multiple rows on the table, as a user can have ancestors from multiple countries.

My question is this: how do I select users whose ancestors hail from multiple, specified countries?

For instance, show me all users who have ancestors from England, France and Germany, and return 1 row per user that met that criteria.

What is that SQL?

 user_id     ancestry

---------   ----------

    1        England
    1        Ireland
    2        France
    3        Germany
    3        Poland
    4        England
    4        France
    4        Germany
    5        France
    5        Germany

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.

Thanks in advance.

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.S. I am looking for users who hail from all 3 countries - England, France, AND Germany (and the countries are arbitrary).

P.P.P.S. I am not looking for answers specific to a certain RDBMS. I'm looking to answer this problem "in general."

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).

Answer 1

Try this:

Select user_id
from yourtable
where ancestry in ('England', 'France', 'Germany')
group by user_id
having count(user_id) = 3

The last line means the user's ancestry has all 3 countries.