If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ANSI SQL > Select data with multiple NOT IN where class

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 1
Select data with multiple NOT IN where class

Hi, Please can anyone help me with below sql query. I need to select data from a table based on multiple NOT IN where conditions
For exmaple- From below table select Emp Id where Dept not in (D1,D2) and Role not in (R1, R2). There is an AND relation between these two condition i.e the condition should be applied only when both the conditions (Dept+Role) are satisfied. For example the query should select Emp id=1 as only Dept = D1 but Role is not R1 or R2. Similarly Emp id= 2 should not be displayed as both the conditions are satisfied.

Emp id Dept Role
1 D1 R3
2 D2 R2
3 D3 R3
4 D1 R3
5 D1 R2
6 D3 R2

Many thanks for your help in advance.
Reply With Quote
  #2 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
Code:
SELECT "Emp Id"
 WHERE Dept NOT IN ( 'D1' , 'D2' ) 
   AND Role NOT IN ( 'R1' , 'R2' )
your explanation of Emp id=1 does not make sense -- why should he be selected again?
__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Location: Issy les Moulineaux, France
Posts: 24
Seems like you need rows where you don't want both dept in D1 or D2 AND role in R1 or R2.
Code:
SELECT "Emp Id"
 WHERE Dept NOT IN ( 'D1' , 'D2' ) 
   OR Role NOT IN ( 'R1' , 'R2' )
Or
Code:
SELECT "Emp Id"
 WHERE NOT ( Dept IN ( 'D1' , 'D2' ) 
             AND Role IN ( 'R1' , 'R2' ) )
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On