Results 1 to 3 of 3
  1. #1
    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  3. #3
    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' ) )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •