help with query

• rash3554

  SSCommitted

  Points: 1984

  More actions

  July 21, 2015 at 4:44 am

  #398771

  Hi there,

  I have table with Company Employee ID and their Position Type and level

  Create table test(ID int, Type varchar(10),level int);

  insert into test(1,'Manager',10);

  insert into test(1,'Non-IT',10);

  insert into test(1,'Non-IT',20);

  insert into test(2,'Manager',10);

  insert into test(3,'Non-IT',20);

  insert into test(4,'Manager',20);

  THis is table that had employee IDS with their Job position type and hierarchical level.

  I want to print employees that are only Non-IT but not Manager irrespective of their hierarchical level i.e. the result is only ID=3. How can I do that?

  Thanks

  Rash

• KS

  Hall of Fame

  Points: 3323

  More actions

  July 21, 2015 at 5:22 am

  #1813799

  Try the below code

  SELECT * FROM Test AS A

  WHERE A.Type = 'Non-IT'

  AND NOT EXISTS

  (SELECT 1 FROM Test AS B

  WHERE A.ID = B.ID

  AND B.Type = 'Manager')

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  July 21, 2015 at 6:48 am

  #1813820

  If you only need the Ids, here's another option.

  SELECT ID

  FROM Test

  WHERE Type = 'Non-IT'

  EXCEPT

  SELECT ID

  FROM Test

  WHERE Type = 'Manager'

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2