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

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.

To clarify: Yes, the user_id / ancestry columns make a unique pair, so a country would not be repeated for a given user. I am looking for users who hail from all 3 countries - England, France, AND Germany (and the countries are arbitrary).

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

I'm content with 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).

sql select

Edit Report

First way: JOIN:

get people with multiple countries:

SELECT u1.user_id 
FROM users u1
JOIN users u2
on u1.user_id  = u2.user_id 
AND u1.ancestry <> u2.ancestry

Get people from 2 specific countries:

SELECT u1.user_id 
FROM users u1
JOIN users u2
on u1.user_id  = u2.user_id 
WHERE u1.ancestry = 'Germany'
AND u2.ancestry = 'France'

For 3 countries... join three times. To only get the result(s) once, distinct.

Second way: GROUP BY

This will get users which have 3 lines (having...count) and then you specify which lines are permitted. Note that if you don't have a UNIQUE KEY on (user_id, ancestry), a user with 'id, england' that appears 3 times will also match... so it depends on your table structure and/or data.

SELECT user_id 
FROM users u1
WHERE ancestry = 'Germany'
OR ancestry = 'France'
OR ancestry = 'England'
GROUP BY user_id
HAVING count(DISTINCT ancestry) = 3

answered 2011-08-24T16:55:36.613

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

1 Answer

First way: JOIN:

Second way: GROUP BY

Your Answer