How to select multiple values from the same table with AND conditions

Question

Assuming the following professor_school table:

professorid, schoolid
1,1
2,1
3,1
4,1
1,2
2,2
5,2
1,3
5,3
...

How can I, using a single query if possible, select every professor that is related to any number of schools?

My input parameters would be an array of unknown size with a list of school ids and i need all the professor ids that are in them all.

Examples:

input 1 - output 1,2,3,4
input 1,2 - output 1,2
input 1,2,3 - output 1
input 2,3  - output 1,5

I don't think i can do inner self joins as the number of input parameters could be variable, my current solution is to group by and count, so

select count(*) as c, professorid from professor_school where schoolid in (1,2,3) group by (professorid) having c=3;

So the question is, is this the only/best solution? Is there a more efficient alternative?

PS: i realize the title of the question is not great but i can't think of a better way to express it.

Answer 1

No, it is not the only solution. If you use MariaDB you could use INTERSECT:

SELECT profesorid FROM professor_school WHERE schoolid = 1
INTERSECT
SELECT profesorid FROM professor_school WHERE schoolid = 2
INTERSECT 
SELECT profesorid FROM professor_school WHERE schoolid = 3

In your example I would also add DISTINCT if you don't have UNIQUE constraint on columns (profesorid/schoolid):

select count(distinct schoolid) as c, professorid 
from professor_school 
where schoolid in (1,2,3) 
group by (professorid) 
having c=3;

Or:

select professorid 
from professor_school 
where schoolid in (1,2,3) 
group by (professorid) 
having count(distinct schoolid)=3;