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).
Note – Use of IN for matching multiple values i.e. TOYOTA and HONDA in the same column i.e. COMPANY. Syntax: SELECT * FROM TABLE_NAME WHERE COLUMN_NAME IN (MATCHING_VALUE1,MATCHING_VALUE2);
Description. The SQL AND condition and OR condition can be combined to test for multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement. When combining these conditions, it is important to use parentheses so that the database knows what order to evaluate each condition.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With