By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,374 Members | 2,993 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,374 IT Pros & Developers. It's quick & easy.

"Except" in Select Statement

P: 6
I need to select cells from one table that do not appear in a second table. I know that this can be done in some flavors of SQL by using Except:

Select Unit_PK From Table1
Except
Select Unit_PK From Table2

This would give me the Unit_PK cells in Table1 that do not exist in Table2.

Microsoft Access apparently does not allow Except or Minus in the SQL statement, so I am wondering if this is possible some other way. Your help is greatly appreciated.

Cheers -

george
Aug 26 '08 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello, George.

The same could be done with outer join.
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.Unit_PK FROM Table1 LEFT JOIN Table2 ON Table1.Unit_PK=Table2.Unit_PK WHERE IsNull(Table2.Unit_PK);
  2.  
Aug 26 '08 #2

100+
P: 167
This is what you should do:
[HTML]Create Query based on Table1 and Table2.
Join between two tables should be set so that ALL records from one table are included and ONLY those from other table where the join fields are the same.
In your case Table1 has them all so it should include all the records from Table1 and only those from Table2 where the join fields are the same.
In WHERE row for the join field from Table2 put : IsNull[/HTML]

The query selects all the records from Table1 and matching records from Table2 leaving blanks in the field from Table2 where the values are missing.
After selecting only those records that are null in field from Table2 you get 'the difference'

This is my test query:
Expand|Select|Wrap|Line Numbers
  1. SELECT MainProfile.EmployeeID, LeaveApply.LaEmployeeID
  2. FROM MainProfile LEFT JOIN LeaveApply ON MainProfile.EmployeeID = LeaveApply.LaEmployeeID
  3. WHERE (((LeaveApply.LaEmployeeID) Is Null));
Aug 26 '08 #3

P: 6
IsNull is the key I was missing. Thank you very much for the comments.

Cheers -

george
Aug 26 '08 #4

FishVal
Expert 2.5K+
P: 2,653
You are quite welcome.

Best regards,
Fish
Aug 27 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.