SQL query with JOIN involving two criteria from same table

I’m working in MS Access trying to iron out an SQL statement that works. On a form I have a combobox that displays a list of employees. I have a separate dialog form that allows the user to select multiple items in a listbox. Each item represents a certification. Each employee can have any number and combination of certifications. Ultimately I just want to update the RowSource property of the combobox to reflect the new filtered data by assigning a proper SQL statement.

If I want to filter the list on the combobox of employees, I use this SQL statement:

SELECT 
    Employees.Employee_ID, Employees.Last_Name, Employees.First_Name 
FROM 
    Employees 
INNER JOIN 
    Emp_Certs ON Employees.Employee_ID = Emp_Certs.Employee_ID 
WHERE 
    (((Employees.Active_Member) = Yes) 
      AND ((Emp_Certs.Employee_ID) = [Employees].[Employee_ID]) 
     AND ((Emp_Certs.Cert_ID) = 1)) 
ORDER BY 
    Employees.Last_Name;

If I run this query it works because I’m assigning only one value to Emp_Certs.Cert_ID. But when I add another like this:

SELECT 
    Employees.Employee_ID, Employees.Last_Name, Employees.First_Name 
FROM 
    Employees 
INNER JOIN 
    Emp_Certs ON Employees.Employee_ID = Emp_Certs.Employee_ID 
WHERE 
    (((Employees.Active_Member) = Yes) 
      AND ((Emp_Certs.Employee_ID) = [Employees].[Employee_ID]) 
      AND ((Emp_Certs.Cert_ID) = 1) 
      AND ((Emp_Certs.Cert_ID) = 4)) 
ORDER BY Employees.Last_Name;

I get an empty set. That’s not what I expected. The table Emp_Certs clearly has several employees that have the combination of certifications 1 and 4. Could someone please explain how this is supposed to be written if I want to indicate more that one Cert_ID and have the employee record show up only once in the combobox. I don’t need the employee records showing up multiple times in the combobox.

This might help:

Answer

When you join tables, you basically query off a result set containing all the combinations of rows from those joined tables that your where clauses then operate off of. Since you are joining to the Emp_Certs table just once and linking only by Employee_ID, you are getting a result set that looks like this (only showing two columns):

Last_Name    Cert_ID
Jones        1
Jones        3
Jones        4
Smith        1
Smith        2

Your where clause then filters those rows, only accepting rows that have Cert_ID = 1 AND Cert_ID = 4, which is impossible so you should not get any rows.

I’m not sure if Access has limititations, but in SQL Server you could handle it in at least two ways:

1) Link to the table twice, joining for each of the certifications. Table alias ‘a’ joins to the Emp_Certs table where the Cert_ID is 1 and table alias ‘b’ joins to the Emp_Certs table where the Cert_ID is 4:

SELECT 
    Employees.Employee_ID, Employees.Last_Name, Employees.First_Name 
FROM 
    Employees 
INNER JOIN 
    Emp_Certs a ON Employees.Employee_ID = a.Employee_ID AND a.Cert_ID = 1
INNER JOIN 
    Emp_Certs b ON Employees.Employee_ID = b.Employee_ID AND b.Cert_ID = 4
WHERE 
    Employees.Active_Member = Yes
ORDER BY Employees.Last_Name;

This gives you a result set that looks like this (Smith doesn’t show up because the join criteria doesn’t allow any rows unless the employee can link to table a and b):

Last_Name    a.Cert_ID   b.Cert_ID
Jones        1           4

2) Use sub-selects in the where clause to filter the employee id on ids with those certifications (looks like Access 2010 supports it):

SELECT 
    Employees.Employee_ID, Employees.Last_Name, Employees.First_Name 
FROM 
    Employees 
WHERE 
    Active_Member = Yes
    AND Employee_ID in (SELECT Employee_ID FROM Emp_Certs WHERE Cert_ID = 1)
    AND Employee_ID in (SELECT Employee_ID FROM Emp_Certs WHERE Cert_ID = 4)
ORDER BY Employees.Last_Name;