INTERSECT multiple subqueries in MS-Access (no INTERSECT keyword)
I need to traverse multiple subqueries in MS Access. Unfortunately Access-SQL does not support the INTERSECT keyword. I understand how we can use an INNER JOIN from two tables to get the intersection we want. But how can I programmatically create a query that will do the intersection of N subqueries?
To be more specific: I have an employee table and a specialization table. Each employee can have several specializations, which means there are many, many relationships between employees and specializations, represented by an additional table containing the identifiers of employees and specializations, nothing complicated.
Now, let me say that I want a list of employees, all of whom have all the specialized assignments that are listed somewhere. In any other SQL core, I would simply create a list of subqueries and use the INTERSECTION keyword to "concatenate" those 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 identifiers corresponding to some specializations. This query returns a set of employees, all of whom have all specializations x_1, ..., x_n. So how do I create a query like this in Access without the INTERSECT keyword. I'm trying to write an equivalent query with an INNER JOIN, but I can't seem to succeed.
source to share
Perhaps this is what you mean. For test charts [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
... request
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
source to share
If you want to do this with joins, you must join one subquery per spec, which only pulls the lines from Emp_spec that match the same spec_id. Note. Access to strange password requirements for multiple connections.
SELECT Employees.id, Employees.first_name
FROM (Employees
INNER JOIN
(SELECT employee_id
FROM Emp_spec
WHERE spec_id = x_1) specx_1
ON Employees.id = specx_1.employee_id)
INNER JOIN
(SELECT employee_id
FROM Emp_spec
WHERE spec_id = x_2) specx_2
ON Employees.id = specx_2.employee_id
...
There's a shortcut in there that you can grab, but you might think it's too kludgey.
SELECT Employees.id, Employees.first_name
FROM Employees
INNER JOIN
(SELECT employee_id
FROM Emp_spec
WHERE spec_id IN (x_1, x_2, x_3, ..., x_n)
GROUP BY employee_id
HAVING COUNT(*) = n) match_counts
ON Employees.id = match_counts.spec_id
The most important part is here HAVING COUNT(*) = n
. You only want employees who have entries n
in the Emp_spec table when the table is filtered only for the specs you need and where n
is the number of specs you are filtering for.
source to share