July 21, 2015 at 4:44 am
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
July 21, 2015 at 5:22 am
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')
July 21, 2015 at 6:48 am
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'
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy