Subquery and NOT EXISTS

Question

Hi all-
I'm hoping someone can help me...I need some good examples of using NOT EXISTS within a subquery in Access SQL.  Here's what I'm trying to do:

I have three tables (I'm assuming all three will be required here, but maybe not):

One (tblStates) is a list of states names and their abbreviations, another (tblAllAssociates) is a list of all Associates and their employee numbers, and the third (tblLicense) is a list that is Employee Numbers and State Abbreviations.

I want to build a query that searches for each employee number from tblAllAssociates and returns each state on tblStates for which there is no entry of employee number and state for that employee.

EG: if tblLicense has employee 123456 showing CA and TX, I'd want my results to show that employee 123456 has no entry for the remaining 48 states (listing each individual state), and so on for each other employee.

Answer 1

Try using an unmatched query.  These use a LEFT JOIN to select ALL records from one table or query and only those that match from the other side of the JOIN.  The missing records are determined by selecting NULL (unmatched) values on the right side of the join.

SELECT a.AssociateName, a.empID, s.State AS MissingState
FROM tblStates LEFT JOIN (tblAllAssociates a INNER JOIN tblLicense l ON l.empid = a.empID) ON l.State = s.State
WHERE l.State IS NULL

---

Miriam Bizup Access MVP

Answer 2

Hi Miriam-
Thanks for the reply.
I've looked at the Unmatched Query Wizard, and using your query as a template, I put this together:

SELECT a.EmpNumber, a.lname, a.fname, states.abbreviation as MissingState
FROM States LEFT JOIN (tblAllAssociates a INNER JOIN tblLicense l 
ON l.EmpNum = a.empNumber)
ON l.State_Abbrev = states.abbreviation
WHERE l.State_Abbrev is NULL

But, Access is saying "JOIN expression not supported".
Any suggestions?

Answer 3

Hi Casey,

There's probably several ways to achieve this. You could try this one:

SELECT SQ.EmpNumber, SQ.Abbreviation
FROM (SELECT T1.EmpNumber, T2.Abbreviation
FROM tblAllAssociates T1, States.Abbreviation T2) AS SQ
LEFT JOIN tblLicense T3
ON SQ.EmpNumber=T3.EmpNumber
AND SQ.Abbreviation=T3.State_Abbrev
WHERE T3.EmpNumber Is Null

(untested)

Hope that helps...

Answer 4

In answer to your original question:

SELECT tblAllAssociates.EmployeeNumber, tblStates.*
FROM tblAllAssociates, tblStates
WHERE NOT EXISTS
    (SELECT *
     FROM tblLicense
       WHERE tblLicense.EmployeeNumber = tblAllAssociates.EmployeeNumber
     AND tblLicense.StateAbbreviation = tblStates.StateAbbreviation)
ORDER BY tblAllAssociates.EmployeeNumber, tblStates.StateAbbreviation;

The outer query returns the Cartesian product of tblAllAssociates and tblStates.  This is then restricted by the subquery to those rows where there is no row in tblLicense with a match on the EmployeeNumber and StateAbbreviation columns.

---

Ken Sheridan, Stafford, England