Pau Lo
asked on
basic query access not in
I have a simple DB with 2 tables (all and exclude), in both tables is an "accountname" field.
I need to run a select * from all where the accountname filed is not equal to the accountname field in the exclude table?
Can anyone give me a heads up on the SQL to acheive something like this in access 2010? None of the join options seemed appropriate in design view.
I need to run a select * from all where the accountname filed is not equal to the accountname field in the exclude table?
Can anyone give me a heads up on the SQL to acheive something like this in access 2010? None of the join options seemed appropriate in design view.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
or perhaps the = is wrong? should it be not =
ASKER
basically if table e includes user 7 , user 32 and user 33
but table a contains user 1... user 100
The output of the query should list all users except user 7, user 32 and user 33.
like a select all rows unless the username is included in the list of usernames in the exlude table.
but table a contains user 1... user 100
The output of the query should list all users except user 7, user 32 and user 33.
like a select all rows unless the username is included in the list of usernames in the exlude table.
Can you post the query exactly as you implemented it in your database?
The LEFT JOIN, combined with the IS NULL in the WHERE clause is the basic setup for an 'unmatched query'.... in this case the goal was to select all records in table a that did not have a matching record in table b (which lists names to exclude)
The LEFT JOIN, combined with the IS NULL in the WHERE clause is the basic setup for an 'unmatched query'.... in this case the goal was to select all records in table a that did not have a matching record in table b (which lists names to exclude)
ASKER
SELECT a.*
FROM [mailboxACL] a LEFT JOIN [exclude] e ON a.Identity = e.acctname
WHERE e.acctname IS NULL
The above has returned entries in the identity column that are entries in the "e.acctname" in the other table.
FROM [mailboxACL] a LEFT JOIN [exclude] e ON a.Identity = e.acctname
WHERE e.acctname IS NULL
The above has returned entries in the identity column that are entries in the "e.acctname" in the other table.
An alternative approach would be:
But the LEFT JOIN method if we can get that working would likely have better performance than the NOT IN method.
SELECT a.*
FROM [All] a
WHERE a.accountname NOT IN (SELECT e.accountname FROM [Exclude] e)But the LEFT JOIN method if we can get that working would likely have better performance than the NOT IN method.
What are the datatypes for Identity and acctname in their respective table designs?
ASKER
worked, was my mistake (uising the wrong field )
thanks
thanks
Glad to help out :-)
ASKER