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

User · Answer

I was having a similar issue like yours  except that I wanted a specific subset of  ancestry   Hong Ning s query was a good start  except it will return combined records containing duplicates and or extra ancestries  e g  it would also return someone with ancestries   England    France    Germany    Netherlands   and   England    France    England    Supposing you d want just the three and only the three  you d need the following query  SELECT Src user id FROM yourtable Src WHERE ancestry in   England    France    Germany       AND EXISTS           SELECT user id         FROM dbo yourtable         WHERE user id   Src user id         GROUP BY user id         HAVING COUNT DISTINCT ancestry    3           GROUP BY user id HAVING COUNT DISTINCT ancestry    3

User · Answer

Users who have one of the 3 countries  SELECT DISTINCT user id FROM table WHERE ancestry IN  England   France   Germany     Users who have all 3 countries  SELECT DISTINCT A userID FROM table A    INNER JOIN table B on A user id   B user id    INNER JOIN table C on A user id   C user id WHERE A ancestry    England     AND B ancestry    Germany     AND C ancestry    France

User · Answer

brute force  and only tested on an Oracle system  but I think this is pretty standard    select distinct usr id from users where user id in       select user id from         Select user id  Count User Id  As Cc       From users        GROUP BY user id       Where Cc  3       and ancestry in   England    France    Germany       edit  I like  HuckIt s answer even better

User · Answer

SELECT DISTINCT  user id   FROM  user  WHERE user user id In  select user id from user where ancestry    England        And user user id In  select user id from user where ancestry    France        And user user id In  select user id from user where ancestry    Germany

User · Answer

This question is some years old but i came via a duplicate to it  I want to suggest a more general solution too  If you know you always have a fixed number of ancestors you can use some self joins as already suggested in the answers  If you want a generic approach go on reading   What you need here is called Quotient in relational Algebra  The Quotient is more or less the reversal of the Cartesian Product  or Cross Join in SQL     Let s say your ancestor set A is  i use a table notation here  i think this is better for understanding   ancestry -----------  England   France   Germany    and your user set U is  user id --------    1    2    3   The cartesian product C AxU is then   user id     ancestry --------- -----------    1        England     1        France     1        Germany     2        England     2        France     2        Germany     3        England     3        France     3        Germany    If you calculate the set quotient U C A then you get  user id --------    1    2    3   If you redo the cartesian product UXA you will get C again  But note that for a set T    T A xA will not necessarily reproduce T  For example  if T is  user id     ancestry --------- -----------    1        England     1        France     1        Germany     2        England     2        France    then  T A  is  user id --------    1    T A xA will then be  user id     ancestry --------- ------------    1        England     1        France     1        Germany    Note that the records for user id 2 have been eliminated by the Quotient and Cartesian Product operations   Your question is  Which user id has ancestors from all countries in your ancestor set  In other words you want U T A where T is your original set  or your table    To implement the quotient in SQL you have to do 4 steps    Create the Cartesian Product of your ancestry set and the set of all user ids  Find all records in the Cartesian Product which have no partner in the original set  Left Join  Extract the user ids from the resultset of 2   Return all user ids from the original set which are not included in the result set of 3    So let s do it step by step  I will use TSQL syntax  Microsoft SQL server  but it should easily be adaptable to other DBMS  As a name for the table  user id  ancestry  i choose ancestor  CREATE TABLE ancestry set  ancestry nvarchar 25   INSERT INTO ancestry set  ancestry  VALUES   England   INSERT INTO ancestry set  ancestry  VALUES   France   INSERT INTO ancestry set  ancestry  VALUES   Germany    CREATE TABLE ancestor   user id  int  ancestry nvarchar 25   INSERT INTO ancestor   user id  ancestry  VALUES  1  England   INSERT INTO ancestor   user id  ancestry  VALUES 1  Ireland   INSERT INTO ancestor   user id  ancestry  VALUES 2  France   INSERT INTO ancestor   user id  ancestry  VALUES 3  Germany   INSERT INTO ancestor   user id  ancestry  VALUES 3  Poland   INSERT INTO ancestor   user id  ancestry  VALUES 4  England   INSERT INTO ancestor   user id  ancestry  VALUES 4  France   INSERT INTO ancestor   user id  ancestry  VALUES 4  Germany   INSERT INTO ancestor   user id  ancestry  VALUES 5  France   INSERT INTO ancestor   user id  ancestry  VALUES 5  Germany     1  Create the Cartesian Product of your ancestry set and the set of all user ids    SELECT a  user id  s ancestry FROM ancestor a  ancestry set s GROUP BY a  user id  s ancestry   2  Find all records in the Cartesian Product which have no partner in the original set  Left Join  and  3  Extract the user ids from the resultset of 2   SELECT DISTINCT cp  user id  FROM  SELECT a  user id  s ancestry       FROM ancestor a  ancestry set s       GROUP BY a  user id  s ancestry  cp    LEFT JOIN ancestor a ON cp  user id  a  user id  AND cp ancestry a ancestry WHERE a  user id  is null   4  Return all user ids from the original set which are not included in the result set of 3   SELECT DISTINCT  user id  FROM ancestor WHERE  user id  NOT IN      SELECT DISTINCT cp  user id     FROM  SELECT a  user id  s ancestry          FROM ancestor a  ancestry set s          GROUP BY a  user id  s ancestry  cp    LEFT JOIN ancestor a ON cp  user id  a  user id  AND cp ancestry a ancestry    WHERE a  user id  is null

User · Answer

like the answer above but I have a duplicate record so I have to create a subquery with distinct  Select user id      select distinct userid    from yourtable    where user id    userid    t1 where  ancestry in   England    France    Germany   group by user id having count user id    3   this is what I used because I have multiple record download logs  and this checks that all the required files have been downloaded

User · Answer

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

User · Answer

one of the approach if you want to get all user id that satisfies all conditions is   SELECT DISTINCT user id FROM table WHERE ancestry IN   England                 GROUP BY user id HAVING count       lt number of conditions that has to be satisfied gt     etc  If you need to take all user ids that satisfies at least one condition  then you can do  SELECT DISTINCT user id from table where ancestry IN   England    France                  I am not aware if there is something similar to IN but that joins conditions with AND instead of OR

User · Answer

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  lt  gt  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

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

Examples related to sql

Examples related to select