INTERSECT multiple subqueries in MS-Access (without INTERSECT keyword)

Question

I need to intersect multiple subqueries in MS Access. Unfortunately, Access-SQL does not support the INTERSECT keyword. I understand, how we can use INNER JOIN of two tables to get the intersection we want. But how can I programmatically create a query that will make the intersection of N subqueries?

To be more specific: I have a table of Employees and a table of Specializations. Each employee can have multiple specializations which means there's a many to many relationship between Employees and Specializations represented by an additional table which contains the ids of employees and specializations, nothing complicated.

Now let's say that I want a list of employees, all of which have all of the specializations specified somewhere. In any other SQL engine I would simply create a list of subqueries and used the INTERSECTION keyword to "join" these subqueries together creating something like:

SELECT * FROM ( 
        (SELECT id, first_name, last_name FROM Employees JOIN Emp_spec 
            ON Employee.id = Emp_spec.spec_id WHERE Emp_spec.spec_id=x_1 )
        INTERSECT 
        ... 
        INTERSECT
        (SELECT id, first_name, last_name FROM Employees JOIN Emp_spec 
            ON Employee.id = Emp_spec.spec_id WHERE Emp_spec.spec_id=x_n )
);

, where x_1,...,x_n represent some ids corresponding to some specializations. This query returns a set of employees, all of which have all the specializations x_1,...,x_n. So how do I create such query in Access without the INTERSECT keyword. I've been trying to write the equivalent query with INNER JOIN but I can't seem to succeed.

SELECT DISTINCT Employees.* FROM Employees INNER JOIN Emp_spec ON Employees.ID = Emp_spec.Employee_ID WHERE (((Emp_spec.Spec_ID) In (1,3))); — tbur
That assumes your linking table has both a [Employee_ID] and a [Spec_ID]. It also assumes the [Spec_id] is stored as a number and not text. If text, they will need to be in single quotes. — tbur
Nope, that's not it. Your returns a set of employees where each employee has at least one of the desired specializations. However, I want all the employees to have all the selected specializations. Btw, your select does the same thing as if you used UNION instead of INTERSECT in my query above. — Coloss
Ah, I did not understand the result had to have ALL of the chosen specializations. SELECT DISTINCT Employees.* FROM (Employees INNER JOIN Emp_spec ON Employees.ID = Emp_spec.Employee_ID) INNER JOIN Emp_spec AS Emp_spec_1 ON Employees.ID = Emp_spec_1.Employee_ID WHERE (((Emp_spec.Spec_ID)=1) AND ((Emp_spec_1.Spec_ID)=3)); In this example, we are filtering for 1 and 3. It is much easier to visualize in the Query Design Grid. The trick is to bring the Emp_Spec table multiple times. One copy for each criteria. Each one joined to the Employee table by ID. — tbur
These comments are getting kind of involved. If you're answering the question, you should post an answer instead of a long comment. — Tmdean

Gord Thompson Gord Thompson · Accepted Answer · 2014-08-20T20:28:06

Perhaps this is what you have in mind. For test tables [Employees] ...

id  first_name  last_name
--  ----------  ---------
 1  Gord        Thompson 
 2  Homer       Simpson  
 3  Hank        Kingsley

... and [Emp_spec] ...

emp_id  spec_id
------  -------
     1        1
     1        2
     2        1
     3        1
     3        2

... the query

SELECT * FROM Employees
WHERE id IN (SELECT emp_id FROM Emp_spec WHERE spec_id=1)
    AND id IN (SELECT emp_id FROM Emp_spec WHERE spec_id=2)

returns

id  first_name  last_name
--  ----------  ---------
 1  Gord        Thompson 
 3  Hank        Kingsley

INTERSECT multiple subqueries in MS-Access (without INTERSECT keyword)

2 Answers