Randhir Rawatlal Randhir Rawatlal - 6 months ago 8
SQL Question

[SQL]: Unique subsets of rows where a column value has each of a set of values

Let's say we have a table with columns c1, c2, c3, ...

For column c2, I am interested in specific values k1, k2, ..., kn.

Consider a subset of rows where each row has the same value of c1 and where if we take the list of values of c2 in the subset, then that list must contain ALL values k1, k2, ..., kn.

What I really want is the list of c1 values of each such subset.

[edit: In response to first comment below:] To provide a basic example, let's say I have a table with userid as column 1 and skillsList as column 2. There are about 15 skills possible in this table. I wish to get the userids of all users who have all of three specific skills, e.g. [sql, php, js]

How do I pull this in SQL please?

Answer

You have described relational division. https://en.wikipedia.org/wiki/Relational_algebra

Let R(c1 PRIMARY KEY, c2, c3) - table
S(col)={ k1, k2, ..., kn} - contains a set values, divider. Two general methods are NOT EXISTS - division with a remainder and COUNT() - exact division.

SELECT DISTINCT c1
FROM R r1
WHERE NOT EXISTS
       (SELECT *
        FROM S 
        WHERE NOT EXISTS
               (SELECT *
                FROM R AS r2
                WHERE (r1.c1 = r2.c1)
                   AND (r2.c2 = S.col)));

and

SELECT r1.c1
FROM R AS r1,
JOIN S AS s1 ON r1.c2 = s1.col
GROUP BY r1.c1
HAVING COUNT(r1.c2) = (SELECT COUNT(s2.col) FROM S s2);

See https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/ for more methods.