Find keys in a sqlite database that meet intersection of conditions

Question

It's hard for me to summarize the query I want to make, so maybe an example will make it clearer.

Let's say I have two primary tables:

employees:

| employee_id | employee_name |
| ----------- | ------------- |
| 1           | Alice         |
| 2           | Bob           |
| 3           | Carol         |

programming_languages:

| language_id | language_name |
| ----------- | ------------- |
| 1           | Python        |
| 2           | C++           |
| 3           | JavaScript    |

And a third table, with foreign keys from the first two, that says which employees know which programming languages:

employee_known_languages:

| employee_id | language_id |
| ----------- | ----------- |
| 1           | 1           |
| 2           | 1           |
| 2           | 2           |
| 2           | 3           |
| 3           | 3           |

So in this example Alice knows Python, Bob knows Python C++ and JavaScript, and Carol knows JavaScript.

If I wanted to write a query to get all the employees who know C++, I know what I'd want to do:

SELECT employee_name from
    employees INNER JOIN programming_languages INNER JOIN employee_known_languages
WHERE language_name = C++

What if I wanted to get all the employees who know C++ and Python? Or, more generally, some arbitrary subset of the values of language_name?

Ideally I'd be able to write this in a way where I can write a Python function that accepts a list of arguments and does the query, but I think just being able to figure out the SQL itself would help.

Wouldn't that get me the programmers who know C++ _or_ Python? In this example query, the result set should be Bob only, not Alice and Bob. — Nathan Pierson, Aug 08 '22 at 21:05
Oh, my fault, bad reading. You're right. I'll get back to you. — Tim Roberts, Aug 08 '22 at 21:08

score 2 · Accepted Answer · answered Aug 08 '22 at 21:15

Use a CTE that returns an arbitrary subset of the values of language_name and filter the results of the joins of the 3 tables for these language_names only.
Then use aggregation and set the condition in the HAVING clause:

WITH cte(language_name) AS (VALUES ('C++'), ('Python'))
SELECT e.* 
FROM employees e
INNER JOIN employee_known_languages ep ON ep.employee_id = e.employee_id
INNER JOIN programming_languages p ON p.language_id = ep.language_id
WHERE p.language_name IN cte
GROUP BY e.employee_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM cte);

See the demo.

Was able to adapt this to my project, including the ability to have the set of target values be of unknown length. Very cool. — Nathan Pierson, Aug 08 '22 at 22:09

score 1 · Answer 2 · answered Aug 08 '22 at 21:20

1

I believe a nested query is required. First get the matches, then count them.

SELECT employee_name, COUNT(*) AS count FROM (
    SELECT e.employee_name, pl.language_name 
    FROM employees e 
    INNER JOIN programming_languages pl 
    INNER JOIN employee_known_languages ekl 
    ON (ekl.employee_id=e.employee_id) and (ekl.language_id = pl.language_id)
    WHERE pl.language_name IN ('Python', 'C++')
)
GROUP BY employee_name
HAVING count > 1;

Output:

sqlite> .read x.sql
Bob|2
sqlite>

answered Aug 08 '22 at 21:20

Tim Roberts

48,973
4
21
30

Would we need to write `count > 2` if there were 3 target languages? – mkrieger1 Aug 08 '22 at 21:25
Exactly. The other solution handles that by creating a temporary table with the target names and using `COUNT` to find the count. – Tim Roberts Aug 08 '22 at 21:26

Find keys in a sqlite database that meet intersection of conditions

2 Answers2